Declaring an active workbook variable Excel Vba
24,800
Dim wB as Workbook
Set wB = Workbooks.Open(xlsMatrixUp)
then you can refer to it as wB.worksheets(4).Activate
for example
Author by
user2569803
Updated on July 12, 2020Comments
-
user2569803 almost 4 years
(Excel VBA)
How would i declare an active workbook variable. or a workbook variable in general.
I have a program flipping back and forth between 2 excel workbooks, and currently we have it just reopen that workbook. But can I just declare it as a variable so i can reference it without re-opening. It would make my life a lot easier .
Thanks in advance!
My current example:
Dim xlsMatrixUp As String fileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls") xlsMatrixUp = fileToOpen Workbooks.Open xlsMatrixUp ActiveWorkbook.Worksheets(4).Activate 'Grabs matrix file 'Goes back to sheet with macro ThisWorkbook.Worksheets(4).Activate ActiveSheet.range("A1").Interior.Color = RGB(128, 128, 128) 'This is off a working if statement Workbooks.Open xlsMatrixUp ActiveWorkbook.Worksheets(4).Activate` 'Returns to matrix and repeats. Any suggestions?
-
user2569803 almost 11 yearsYou sir, deserve a pat on the back
-
Julien Marrec almost 11 yearsYou're very welcome. Obviously, you can also declare a worksheet and refer to it right away.
Set wS = wB.Worksheets(4)
and then refer to it directly aswS.Range("A1")