How do I change the worksheet referenced to be a variable? I want it to activate

Question

The macro I wrote - it pulls information from two different spreadsheets and formats it all. Those spreadsheets will change every month. How do I get the "ACTIVATE" command to reference the worksheet I have open instead of the one that WAS open when I recorded this macro? One of the commands loos like this:

Windows("273517_0273517M190831_08_31_2019_Toll Detail.csv").Activate

I tried to copy and paste a few lines of the code but THIS site rejected it, saying it wasn't formatted properly. Since it was an exact copy of a fully functioning macro - I haven't got a clue what it wants.

It is the "...Toll Detail.csv" file in line 4 that needs to be a variable as it changes monthly. This file is referenced several times in my recorded macro. I will need to be able to go in and change each of them in whatever manner you suggest. I am TOTALLY NEW to VBA so please, dumb it down as best you can. :) Thank you!

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Sep 19 '19 at 08:46

score 0 · Answer 1 · answered Sep 19 '19 at 08:50

I would suggest declaring a variable in the beginning of the sub, like so:

Dim strFile As String
strFile = "273517_0273517M190831_08_31_2019_Toll Detail.csv"

Throughout the sub you can reference the variable, so in every instance where there is the workbook name in the code replace it with strFile like this:

Windows(strFile).Activate

That way you only have to update it once for the entire procedure. There might be more elegant solutions, but more information on the use case would be required for that.

score 0 · Accepted Answer · answered Sep 19 '19 at 11:30

0

You can use GetOpenFileName to select and open your file each time. Following is the code for your reference:

Sub OpenFile()
Dim myFile As String
Dim Wb As Workbook

myFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv")

'Exit if no file selected
If myFile = "False" Then
    MsgBox "No file selected!"
    Exit Sub
End If

'Open the file
Set Wb = Workbooks.Open(myFile)
'Do what you want with the file here onward
'
'
'
End Sub

answered Sep 19 '19 at 11:30

Dhirendra Kumar

333
2
5

Will this work if I have 2 workbooks open at the same time? – Kate F Sep 20 '19 at 16:48
Yes. You may use variables to hold the reference of multiple files and handle them as required. – Dhirendra Kumar Sep 21 '19 at 17:53
How do I thank someone for their excellent input?? Your suggestion was perfect! – Kate F Sep 23 '19 at 12:20

How do I change the worksheet referenced to be a variable? I want it to activate

2 Answers2