Excel VBA Macro to Use a 3rd Workbook to Transfer Data between 2 Workbooks

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

Sub ABC()

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:

a.Sheets("Sheet1").Range("A1").Copy

'Now, paste in to b worksheet:

b.Sheets("Sheet1").Range("A1").PasteSpecial

End Sub

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