Store the active worksheet(name) into variable 
Author Message
 Store the active worksheet(name) into variable

How would I store the activeworksheet as a variable for VBA.
This is not catching it
Dim ASeet as WorkSheet
Set ASheet = Application.ActiveWorkbook.ActiveSheet

I need to have VBA set the current sheet within a book as a variable.
Whatever sheet I am on, when I run the program, the current sheet is the
activesheet and thus is stored within a variable.

This should allow me to select another sheet on a different, open
workbook and then go back.

Illistration--
Variable: ASheet
Set ASheet = Book1 Sheet1 <--this will change depending on what sheet I
am currently on.

If the program selects Book 2, I then need the program to go back to
ASheet.  For example, I am copying a cell's data on ASheet and pasting
it to Book 2, I need to go back to the original book.

thanks

Rich Atlanta
XL97



Mon, 21 Aug 2000 03:00:00 GMT  
 Store the active worksheet(name) into variable

Hi Rich,

The following code works fine for me:

    Set x = ActiveSheet
    Workbooks("Book2").Worksheets(1).Activate
    Range("E4").Select
    x.Activate

If you are trying to return using another sub, you will need to make x a
module level variable, not a local variable, which is destroyed when the
sub ends.

Dim x As Worksheet

Sub GoForward()
    Set x = ActiveSheet
    Workbooks("Book2").Worksheets(1).Activate
    Range("E4").Select
End Sub

Sub GoBack()
    x.Activate
End Sub

HTH,

John Green - Excel MVP
Sydney
Australia

Quote:

> Date: Thu, 05 Mar 1998 18:30:19 -0500

> Subject: Store the active worksheet(name) into variable
> Newsgroups: microsoft.public.excel.programming

> How would I store the activeworksheet as a variable for VBA.
> This is not catching it
> Dim ASeet as WorkSheet
> Set ASheet = Application.ActiveWorkbook.ActiveSheet

> I need to have VBA set the current sheet within a book as a variable.
> Whatever sheet I am on, when I run the program, the current sheet is the
> activesheet and thus is stored within a variable.

> This should allow me to select another sheet on a different, open
> workbook and then go back.

> Illistration--
> Variable: ASheet
> Set ASheet = Book1 Sheet1 <--this will change depending on what sheet I
> am currently on.

> If the program selects Book 2, I then need the program to go back to
> ASheet.  For example, I am copying a cell's data on ASheet and pasting
> it to Book 2, I need to go back to the original book.

> thanks

> Rich Atlanta
> XL97



Tue, 22 Aug 2000 03:00:00 GMT  
 Store the active worksheet(name) into variable

You can use an ordinary string.
Dim Asheet, Abook As String
Abook = ActiveWorkbook.Name
Asheet = ActiveSheet.Name
'Later on:
Windows(Abook).Activate
Worksheets(Asheet).Activate

Quote:

>How would I store the activeworksheet as a variable for VBA.
>This is not catching it
>Dim ASeet as WorkSheet
>Set ASheet = Application.ActiveWorkbook.ActiveSheet



Tue, 22 Aug 2000 03:00:00 GMT  
 Store the active worksheet(name) into variable

You can use an ordinary string.
Dim Asheet, Abook As String
Abook = ActiveWorkbook.Name
Asheet = ActiveSheet.Name
'Later on:
Windows(Abook).Activate
Worksheets(Asheet).Activate

Quote:

>How would I store the activeworksheet as a variable for VBA.
>This is not catching it
>Dim ASeet as WorkSheet
>Set ASheet = Application.ActiveWorkbook.ActiveSheet



Tue, 22 Aug 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Referencing a variable name stored in a variable

2. Can't extract data from another worksheet using the worksheet name as a variable

3. Storing active subform in variable

4. Store in a variable a portion of a worksheet

5. Store in a variable a portion of a worksheet

6. naming active worksheets

7. active worksheet name

8. Active worksheet name

9. Variables stored in Range Names

10. Setting a control who's name is stored in a variable


 
Powered by phpBB® Forum Software © phpBB Group