How to switch between workbooks in vba? Working on one summary file and multiple workbooks.

[ironny90]

Hi everyone! This is the first week I learn VBA so forgive me if my questions are dumb. So I am working on multiple templates with one summary file (named "Test"). So I need to copy values from the summary file and paste to each individual workbooks. Cell A1 in each workbook contains the individual name, which is also in column A of the summary file.

My idea is to open each workbook, go to cell A1, get the value and search the value in the summary file, then offset from that cell to get the value I want, copy and paste to the individual workbook.

But I ran into error at the line in bold. Also how do I switch back to the other workbook since I activated the summary file called "Test"? I don't necessarily know each workbook name in test1 folder. They just run one by one... Let me know if you can help with how to proceed Thank you!

Sub Macro1()
Set fso = CreateObject("scripting.filesystemobject")
Set ff = fso.getfolder("C:\Users\34264\Desktop\training\Winston\Winston_1\test1")
For Each file In ff.Files
Workbooks.Open file
Sheets("Summary").Select
Range("A1").Select

rngY = Range("A1").Value

Workbooks("Test.xlsx").Activate -- error here
Sheets("test").Select
Columns("A:A").Select
Selection.Find(What:=rngY, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select


Next

End Sub

 

[ironny90]

oki, found solution to refer to the other open workbook, I can set wbk2 = activeworkbook and just refer to wbk2 going forward.