Worksheet Links-Next Open Cell (2000)

Topic

New Reply

Question-
I download (copy/paste) a spreadsheet each business day that shows daily closing prices on certain futures contracts. I would like to be able to continue to do so, and then link the current day’s data to another spreadsheet that “archives” all prior days. For example, assume I download (cut/paste) the closing prices on day 1 for twelve periods (months) in cells A1:A12 into Worksheet No. 1. I would like to link this data to Worksheet No. 2, cells A1:L1. Then, I would proceed to download day 2 data (cut/paste) into the same cells (i. e., A1:A12) on Worksheet No. 1, but I want the link on Worksheet No. 2 to load the day 2 data from cells A1:A12 on Worksheet No. 1 into cells A2:L2 in Worksheet No. 2, with subsequest days being cut and pasted into the same area on Worksheet No. 1, but placed into subsequent rows in Worksheet No. 2.

Any ideas?

Thanks,

Jeff

Reply | Quote

Replies

You can make a macro attached to a button on sheet1 that – when clicked – copies the data in the cells you want and paste them into sheet2 on the first empty row below the Sheets(“Sheet2”).Range(“A1”).Currentregion. I don’t know if you want to use macros, but another way does not immediately cross my mind.

Reply | Quote

I don’t mind, but I have never (and I mean NEVER) done a Macro before. Could you walk me through it from step 1 to the end?
Thanks,
Jeff

Reply | Quote

OK. Let’s try.
Make a workbook with 2 worksheets. Name one e.g. ‘Daily Download’ and the other ‘DataBase’. Add a button to the ‘Daily Download’ worksheet. (you can do this by choosing View >> Toolbars >> Control Toolbox and drag a button from the toolbox to the sheet). In ‘Design’ mode (toggle the design button on the control toolbox to change from design mode to runtime mode), change the name of the button (this is the name of the button itself, not the caption you see). You can do this by selecting the ‘properties’ icon on the control toolbox while the button on the sheet is selected. (the property icon is right from the design icon). Change the name of the button to CmdTransfer and change the caption to ‘Transfer to Database’. Then, still in design mode, double-click the button and you will enter the code page, where you add the following code

Private Sub CmdTransfer_Click()
   ActiveSheet.Range("A1:A12").Copy
   Sheets("DataBase").Select
   Sheets("DataBase").Range("A1").Select
   ActiveCell.End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, , True
End Sub

Actually, the first and last line of the code should already be there. So, you only need to add the code in between. Now, return to Excel (via File >> Close and Return to Microsoft Excel). Change from design mode to runtime mode and clicking the button should do what you want.

Now, I’ll explain the few lines of code: as the button is placed on the sheet named ‘Daily Download’ and this sheet is now active, when the button is clicked, the first line of code copies the range A1:A12 from the activesheet to the clipboard. The next line selects the Sheet named ‘DataBase’. The next line select the cell A1 on that sheet, which is now the activecell. The last line does a few things at the same time. Activecell.End(xldown) positions the cursor in the last non-empty cell down starting from the activecell. If an empty cell is found, the code thinks that this is the last cell and will position the cursor immediately above that cell. Offset(1,0) goes 1 row down (0 columns right). This should be the first empty line, where the contents of the clipboard can be pasted (with pastespecial). The pastespecial method has 4 arguments of which only the last is important here. This is the Transpose argument, which should be set to TRUE otherwise the data is pasted in a column instead of in a row.

Hope this helps you understand this simple macro and encourages you to experiment a little bit with it.

Reply | Quote

OK, some questions (please be patient-these will probably sound pretty absurd):
1. Which “button” do I drag from the toolbox to the sheet?
2. How do I “toggle” the design button-merely click it?
3. If you don’t mind, I may want to complicate thing a bit. What if the “Daily Download” worksheet actually contained six (6) comlumns (Cells A2:F2, say) of data (corresponding to a particular month’s “Opening Bid”, “Daily High Bid”, “Daily Low Bid”, “Daily Close Price/Bid”, “Volume Of Contracts Traded” and finally “Ending Contracts Outstanding”), with seventy-two (72) rows corresponding to the number of months that are/were traded on that particular day (i. e., February 2002-January 2008). I would like each of the rows on the Daily Download Worksheet to then be entered on the “DataBase” Worksheet in columns that would correspond to the months. For example, Cells A2:F2 in the Daily Download Worksheet would correspond to the February 2002 Contract, and I would like this data to show up in the DataBase Worksheet at Cells C2:H2. The daily March data that would show up at A3:F3 on the Daily Download Worksheet would then show up at I2:N2. The daily April 2002 contract data, appearing on the Daily Download Worksheet at A4:F4, would then be comlpiled in the DataBase Worksheet beginning at O2:T2, and so on. Does this pose any additional problems?
Thanks again for your time and patience.
Jeff

Reply | Quote

Jeff,

Maybe you should attach an example of your workbook.
To answer your first and second question: if you hold the cursor a few seconds on the tool then the tooltip will tell you which control you are pointing at. You need to click the ‘button’ control and drag it to your worksheet. If you do so, you can change the properties of this button control in ‘design mode’. You have to experiment a little bit to find out what it means. You go from design mode to runtime mode by just clicking the ‘design’ control on the control toolbox. So, toggling is just clicking.

It is clear that macros can be written to do what you want, as complicated as you want, but if your are really new in writing macros, then I suggest to start easy. Try to make the example in my previous post working. Let’s then come back to the complication.

Reply | Quote

I gave it a shot, but as you can see from the attached, I didn’t do something right-it returns a “Run-time error’1004′: Application-defined or object-defined error”.

Reply | Quote

OK, something went wrong with the activecell.end(xldown). It worked for me, but maybe it is safer to change the code as follows:
Go in the code window (by double clicking the button while you are in ‘design’ mode) and change the following line: (here we select the A-cell in the last row (row 65536) of your sheet)

Sheets(“DataBase”).Range(“A65536“).Select

Now, replace xlDown with xlUp in the next line.

ActiveCell.End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, , True

This should work for you.

Reply | Quote

What the heck am I doing wrong!!!!! I did what you said, but when I clicked on the button, the system merely placed the little “sizing” boxes around the button as if it thought I wanted to resize it! Then when I double-click on it, the program says the macros have been disabled…
I told you I’m not very good at this!

Reply | Quote

try this..maybe this is what you looking for ..hth

Reply | Quote

Thanks for trying, but I encountered 2 problems-first, the data on the DataBase worksheet needs to be transposed from columnar on the Daily Download worksheet to row form on the DataBase worksheet-on your version, it doesn’t do this. Second, how can I “enable the macros”? Even after I go and run the macro, the system still comes back and says the macros are disabled.

Reply | Quote

Sorry-here is the attachment.

Reply | Quote

Maybe this.or maybe not..a

About the macro problem :

if you using excel 2000
go tools==>macro==>security==>security level.and pick low (not recommended)..
it will ignored the popup box..

but if you are using excel 97 : i can’t remember it
something like go under tools ==>macro..and then..i cant remeber

hth

Reply | Quote

Nope-doesn’t do the trick! (At least not for me!)

Reply | Quote

Jeff,

What you did wrong was that you changed the wrong statement. Leave the statement

Sheets(“DataBase”).Select

as it was and change the statement below to:

Sheets(“DataBase”).Range(“A65536”).Select

The first statement is needed to make the DataBase sheet active in the first place.

What happened to make your macro dissappear, I don’t know, try to enter it again.
If the system placed the little ‘sizing’ boxes around the button, this is because you are in ‘design’ mode.
Don’t give up.

Reply | Quote

Here it what you should get.

Reply | Quote

We are there!!!!! Thanks for all of your guy’s help and patience!!!!!
Now, on to customizing the code-any takers to tackle the daily download of 6×72 as per my earlier message to Hans?
Thanks again,
Jeff

Reply | Quote

The 12 rows are there because I tried it out several times. And, indeed each time you click the button the data in the Daily Download sheet are copied and added as a row in the DataBase worksheet. I thought that was the intention: to add the data, not to replace it. If you want to get rid of the data in the DataBase, just delete all the rows and try again the button: the data should now be added to the second row (the first is left for your headings, still to be added)

Reply | Quote

Hans-no problem-I edited my post after realizing the error of my ways (words).
Thanks,
Jeff

Reply | Quote