Thursday, 23 August 2018

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

No comments:

Post a Comment