Variable Worksheet Name

[Azar]

I wrote/recorded a Macro that works with various worksheets.
the main worksheet's name changes each time i use the macro (its a file that records daily data, each day it has a new name, example' MetricsDate 1_1_2000, 1_2_2000 etc)

Part of the Macro includes opening other worksheets/files to bring data into them etc. When the macro needs to activate a different window it is coded to always refer back to 'MetricsDate 1_1_2000' but that was only the name of the file when i recorded the macro, and it is not the name of the file each subsequent time i use the macro. How can i have the macro recognize and activate that window when it has a different name


thanks.

 

[VoG]

You can use

ThisWorkbook

to refer to the workbook containing the code.

 

[Azar]

thanks,
but the macro is saved in a 'personal' file.
also, other worksheets are open at the same time.
and the macro refers back to the original/main worksheet a few times while the macro is running, and i think it would not know which worksheet it is....

 

[VoG]

You need to post your code.

 

[Joe4]

The logic is to "capture" the name of the workbook or worksheet when you first access it, then you can refer back to it later (regardless of what it is named).

For worksheets, it would look something like this:

    Dim myWS As Worksheet
    Set myWS = ActiveSheet

Then, if you want to refer back to this sheet later, you can do so like this:

    myWS.Activate

 

[Azar]

You need to post your code.

VoG- below is the relevant parts of the code.
the name that is variable is
Windows("Daily Metrics Template_beta.xlt").Activate
I put it in caps below.
that file has a different name each time i open it and run this macro.

Sub Daily_Metrics_Setup()
Sheets("BOA Logs").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("A:L").Select
Columns("A:L").EntireColumn.AutoFit

(there are more items here, but not important, all part of the same sheet as above)
then here is were i hit my problem- I open new excel files and copy from the original active workbook into it

ChDir "C:\Documents and Settings\Alan\Desktop\Codetest62"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Alan\Desktop\Codetest62\boalogs.csv"
Range("I2:M500").Select
Selection.ClearContents
WINDOWS("DAILY METRICS TEMPLATE_BETA.XLT").ACTIVATE
Sheets("BOA Logs").Select
Range("B1:B200,F1:F200,K1:K200,E1:E200").Select
Selection.Copy
Windows("boalogs.csv").Activate
Columns("J:M").Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Save
ActiveWindow.Close
ChDir "C:\Documents and Settings\Alan\Desktop\Codetest62"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Alan\Desktop\Codetest62\citilogs.csv"
WINDOWS("DAILY METRICS TEMPLATE_BETA.XLT").ACTIVATE
Application.CutCopyMode = False
Sheets("Citi Logs").Select
Range("B1:B300,E1:E300,F1:F300,K1:K300").Select
Range("K46").Activate
Selection.Copy
Windows("citilogs.csv").Activate
Columns("J:M").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("A1").Select

 

[Azar]

The logic is to "capture" the name of the workbook or worksheet when you first access it, then you can refer back to it later (regardless of what it is named).

For worksheets, it would look something like this:

    Dim myWS As Worksheet
    Set myWS = ActiveSheet

Then, if you want to refer back to this sheet later, you can do so like this:

    myWS.Activate

thanks!
what about if the variable is a window. can I just code the following:?

Dim myWindow As Window
Set myWindow = ActiveWindow

 

[RichardMGreen]

You'd probably be better off setting up objects and referencing those.
So your code would become:-

Sub Daily_Metrics_Setup()
Dim ThisBook as Workbook,otherbook as workbook
set thisbook=activeworkbook
Sheets("BOA Logs").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("A:L").Select
Columns("A:L").EntireColumn.AutoFit

(there are more items here, but not important, all part of the same sheet as above)
then here is were i hit my problem- I open new excel files and copy from the original active workbook into it

set otherbook=Workbooks.Open Filename:= _
"C:\Documents and Settings\Alan\Desktop\Codetest62\boalogs.csv"
otherbook.activesheet.Range("I2:M500").ClearContents
thisbook.Sheets("BOA Logs").Range("B1:B200,F1:F200,K1:K200,E1:E200").Copy
otherbook.Activate
Columns("J:M").Select
ActiveSheet.Paste
application.cutcopymode=false
otherbook.close false   'change to true if you want to savce the CSV file
set otherbook=Workbooks.Open Filename:= _
"C:\Documents and Settings\Alan\Desktop\Codetest62\citilogs.csv"
thisbook.Sheets("Citi Logs").Range("B1:B300,E1:E300,F1:F300,K1:K300").Copy
otherbook.Activate
Columns("J:M").Select
ActiveSheet.Paste
Application.CutCopyMode = False
otherbook.Close false   'change to true if you want to savce the CSV file
Range("A1").Select

I haven't tested this code, but it shouldn't be too far adrift.