Copy from a open workbook with variable name into the workbook I run the macro from

Question

I want to make a macro that copies data from an open workbook that I receive via Email. The workbook name always starts with "Open Order Monitoring"[...] and end with a variable. I want the macro to recognize the first three words and then choose the right open workbook to copy from. I want to run the macro from the workbook the data gets pasted in:

Sub CopyData()
Dim Wb1 As Workbook, wb2 As Workbook

'Copy from open workbook that always start with the name 
'Open Order Monitoring[...]'. I need a variable
Set Wb1 = XXXXXXXX 'needs a variable here I think

'Paste into the Workbook I run the macro from (aka ThisWorkbook??)
Set Wb2 = ThisWorkbook

'Copy Data from Wb1.Sheet1 to Wb2.sheet2
Wb1.Sheets(1).Range("A2").Range(.Cells(1, 1), 
.End(xlDown).Cells(1, 39)).Copy wb2.Sheets(2).Range("B5")

End Sub

I don't know much about VBA...

tl;dr - Copy from one open workbook (with variable name) into another open workbook (I run the macro from)

Dave Dave · Accepted Answer · 2016-05-18T12:33:25

Look through the open workbooks and select the one which matches your requirements:

For Each wB in Application.Workbooks
    If Left(wB.Name, 21) = "Open Order Monitoring" Then
        Set Wb1 = wB
        Exit For
    End If
Next

That will give you the first open workbook starting with that name and set it to Wb1 for you.

Copy from a open workbook with variable name into the workbook I run the macro from

1 Answers