Copy from preceding worksheet

[Barry Katcher]

Hi, guyz & galz.

I have 12 identically set up monthly worksheets, named Jan thru Dec. I would like to be able to, by code, go to the preceding worksheet, regardless of it's name, select and copy a range, then return to the original worksheet and paste.

I think I can just record a macro to do the copying and pasting, but I need those two little lines of code to take me to the preceding worksheet and then take me back to the originating worksheet.

If I can do that, it will only take one macro instead of separate codes for each of the eleven sheets (Feb thru Dec.)

Thanx in advanx,

Barry

 

[HalfAce]

Hi Barry,
A couple things to note. If all you're doing is copying/pasting then you don't need to select
either the sheet or the range. (So you won't need to refer back to the originating sheet.)

A quick example of that might look like this.

Sub Demo()
'Next sheet to the left
Sheets(ActiveSheet.Index - 1).Range("A3:D3").Copy Range("A15")

'Next sheet to the right
Sheets(ActiveSheet.Index + 1).Range("A3:D3").Copy Range("A16")
End Sub

However, if you try to refer to ActiveSheet.Index + 1 when you're already in the last
sheet it'll generate an error. Same with trying to refer to ActiveSheet.Index - 1 from the first sheet.
Will you require a bit of error trapping for that or will it not be an issue?

 

[Datsmart]

This code should help.

    Set OldActive = ActiveSheet
    ActiveSheet.Previous.Select
'your stuff here...
    OldActive.Activate  'Return to sheet you came from

 

[Barry Katcher]

Thanks, HalfAce and John. I'll try them out when I get to work in an hour or so, and let you know.

 

[Barry Katcher]

Thanks, guyz. Both of them worked a treat.