Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to copy cells from previous worksheet (XL 2002 SP3)

    The Worksheet object has Next and Previous properties, returning the next and previous worksheet. So you can use

    Worksheets("MySheet").Previous.Range("A1100").Copy Destination:=Worksheets("MySheet").Range("A1")

    or something similar.

  2. #2
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to copy cells from previous worksheet (XL 2002 SP3)

    Hi Hans,

    I just tried your code, but it gave an error. I copied the error message and the highlighted lines from the code into the attachment. Have I overlooked something ? <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

    Regards,

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to copy cells from previous worksheet (XL 2002 SP3)

    Hi again Hans,

    I just found that, when I replace "MySheet" with the actual name of the sheet (in my case "tot 30 november 2004") the code does work.
    But, I do not want to adjust the code every week (not every month as I indicated in my original post) with the correct name of the sheet (It will be "tot 7 december 2004" next week on a new inserted sheet with data to copy from "tot 30 november 2004", and so on).

    So, is there a way to let the macro know that the code has to be executed starting from the active sheet (this week "tot 30 november 2004" and next week "tot 7 december 2004"), and then copy the wanted cells from the previous sheet (this week "tot 23 november 2004" and next week "tot 30 november 2004")?

    I really hope you can help me ! <img src=/S/help.gif border=0 alt=help width=23 height=15>

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to copy cells from previous worksheet (XL 2002 SP3)

    Just replace Worksheets("MySheet") with ActiveSheet.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to copy cells from previous worksheet (XL 2002 SP3)

    <font color=blue> Original post edited 01 dec 2004 by Walter </font color=blue>

    Hi all,

    In my workbook, I need to copy every week some defined cells to a new inserted worksheet. The cells containing the wanted data are on the "previous" worksheet. I would like to automate this with a macro. Problem is : how do I tell the macro to fetch the data from the "previous" worksheet, as (and now the problem gets even bigger) the names of the worksheets are not "sheet1", "sheet2" and so on. They have (more or less "illogical" names.

    I tried to record the macro, but this gave the code

    <font color=blue> Sheets("illogical name").Select </font color=blue>

    And this will not be OK when I would run the macro next week.

    Any help on how to point the code to the "previous" worksheet ?

    MTIA

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to copy cells from previous worksheet (XL 2002 SP3)

    Alternatively, if you want to refer to the next to last worksheet in the workbook, use

    WorkSheets(WorkSheets.Count - 1)

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to copy cells from previous worksheet (XL 2002 SP3)

    It works !! <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

    Thanks again, Hans ! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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