Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Worksheet Links-Next Open Cell (2000)

    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

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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

    <pre>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
    </pre>


    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.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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".
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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!
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

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

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    Sorry-here is the attachment.
    Attached Files Attached Files

  13. #13
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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
    Attached Files Attached Files

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

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

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet Links-Next Open Cell (2000)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •