Referring to the previous selected sheet in a macro

[michaelberrier]

Is there anyway to refer to the previous selected sheet in a macro?

In other words, the macro is designed on a sheet named "Master" and
switches back and forth between that sheet and another, lets call it
"Output".

"Master" is designed to be copied and renamed, but it always references
"Output" in certain macros.

The code currently begins on "Master" and goes something like this:

Range("A1:A10").Copy
Sheets("Output").Select
Range("A1:A10").Paste
Sheets("Master").Select
....and so on.

I need that same functionality if "Master" is named "Master2" or
"AbeVigoda" or whatever. Does VBA include syntax for Previous Sheet or
Last sheet or anything so that you can tell the macro to go back to
sheet it started from?

Thanks to all.

[Jim Cone]

Assuming the ActiveSheet is the start sheet then just assign the
sheet name to a string variable....
Dim strSheetName as String
strSheetName = ActiveSheet.Name
Use it like this...
WorkSheets(strSheetName).Range("xyz").Copy
-or-
Set an Object Variable to the ActiveSheet...
Dim objSheet as Excel.Worksheet
Set objSheet = ActiveSheet
Use it like this...
ObjSheet.Range("xyz").Copy
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"michaelberrier"
<michaelberrier@gmail.com>
wrote in message
Is there anyway to refer to the previous selected sheet in a macro?
In other words, the macro is designed on a sheet named "Master" and
switches back and forth between that sheet and another, lets call it
"Output".
"Master" is designed to be copied and renamed, but it always references
"Output" in certain macros.
The code currently begins on "Master" and goes something like this:

Range("A1:A10").Copy
Sheets("Output").Select
Range("A1:A10").Paste
Sheets("Master").Select
....and so on.
I need that same functionality if "Master" is named "Master2" or
"AbeVigoda" or whatever. Does VBA include syntax for Previous Sheet or
Last sheet or anything so that you can tell the macro to go back to
sheet it started from?
Thanks to all.

[michaelberrier]

Absolutely PERFECT! Thanks a lot.

Jim Cone wrote:
> Assuming the ActiveSheet is the start sheet then just assign the
> sheet name to a string variable....
> Dim strSheetName as String
> strSheetName = ActiveSheet.Name
> Use it like this...
> WorkSheets(strSheetName).Range("xyz").Copy
> -or-
> Set an Object Variable to the ActiveSheet...
> Dim objSheet as Excel.Worksheet
> Set objSheet = ActiveSheet
> Use it like this...
> ObjSheet.Range("xyz").Copy
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "michaelberrier"
> <michaelberrier@gmail.com>
> wrote in message
> Is there anyway to refer to the previous selected sheet in a macro?
> In other words, the macro is designed on a sheet named "Master" and
> switches back and forth between that sheet and another, lets call it
> "Output".
> "Master" is designed to be copied and renamed, but it always references
> "Output" in certain macros.
> The code currently begins on "Master" and goes something like this:
>
> Range("A1:A10").Copy
> Sheets("Output").Select
> Range("A1:A10").Paste
> Sheets("Master").Select
> ...and so on.
> I need that same functionality if "Master" is named "Master2" or
> "AbeVigoda" or whatever. Does VBA include syntax for Previous Sheet or
> Last sheet or anything so that you can tell the macro to go back to
> sheet it started from?
> Thanks to all.