Copy from preceding worksheet

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
Code:
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This code should help.
Code:
    Set OldActive = ActiveSheet
    ActiveSheet.Previous.Select
'your stuff here...
    OldActive.Activate  'Return to sheet you came from
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Thanks, HalfAce and John. I'll try them out when I get to work in an hour or so, and let you know.
 

Forum statistics

Threads
1,136,427
Messages
5,675,784
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top