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
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