In this very simple example, I have an Excel Workbook called C.xlsx.
In C.xlsx, I list the workbook names A.xlsx (in cell A1) and B.xlsx (in cell B1).
I have a bit of data in cell A1 of A.xlsx that I want to ship to A1 in B.xlsx and running the Macro actions this!
Note: The 3 workbooks need to be open (I don’t open/close in this example.)
Dim a As Workbook
Dim b As Workbook
Dim c As Workbook
'## Open C workbook first:
Set c = Workbooks("C.xlsx")
Dim SrcFile As String: SrcFile = c.Sheets("Sheet1").Range("A1").Value
Dim DstFile As String: DstFile = c.Sheets("Sheet1").Range("B1").Value
'## Open A and B workbooks:
Set a = Workbooks(SrcFile)
Set b = Workbooks(DstFile)
'Now, copy what you want from a:
'Now, paste in to b worksheet:
I’m a noob using Excel VBA Macro’s, so yes it is a bit of basic example but it’s a start. Now I’ve got to write what I actually need (which is much more complicated - requires a prompt, many more fields, saving a file with a specific filename ...).
Image: ABC Excel VBA Macro Example