Copy worksheet from outside workbook using path located in cell

Hello,

I have some code here that isn't working and I'm not sure why... I'm trying to copy a worksheet from an outside workbook (the path and filename are located in B2 and B3 of the "Info" tab. I keep getting an "Compile error: Expected: end of statement" message when it gets to the ":=" in the Set CopyFromWbk line.

Dim fPath As String
Dim fName As String
Dim CashFile As String
fPath = Sheets("Info").Range("B2").Value
fName = Sheets("Info").Range("B3").Value
CashFile = fPath & "\" & fName
Dim CopyFromBook As String
Dim CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Set CopyFromWbk = Workbooks.Filename:=CashFile
Set ShToCopy = CopyFromWbk.Worksheets("IAG")
Set CopyToWbk = ActiveWorkbook
 
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)

If someone could help, that'd be great.

Thanks!

Does Range("B3") have a file extension?

Yep. It's .xlsx and it's located in the cell with the rest of the filename.

Maybe?

Sub freybe06()
Dim fPath As String
Dim fName As String
Dim CashFile As String
fPath = Sheets("Info").Range("B2").Value
fName = Sheets("Info").Range("B3").Value
CashFile = fPath & "\" & fName
Dim CopyFromBook As String
Dim CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Set CopyFromWbk = Workbooks(CashFile)
Set ShToCopy = CopyFromWbk.Worksheets("IAG")
Set CopyToWbk = ActiveWorkbook
 
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.count)
End Sub

I'm getting a "Subscript out of range" error for "Set CopyFromWbk = Workbooks(CashFile)"

Are you intending to add a workbook? Or does it already exist and your trying to open it?

Both workbooks already exist. There is the CopyTo workbook (the one with the macro) and the CopyFrom workbook (the one that the macro opens up to take a spreadsheet from).

Then maybe using your first code.

Dim fPath As String
Dim fName As String
Dim CashFile As String
fPath = Sheets("Info").Range("B2").Value
fName = Sheets("Info").Range("B3").Value
CashFile = fPath & "\" & fName
Dim CopyFromBook As String
Dim CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Set CopyFromWbk = Workbooks.Open Filename:=CashFile
Set ShToCopy = CopyFromWbk.Worksheets("IAG")
Set CopyToWbk = ActiveWorkbook
 
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)

You left off the .Open

Thats giving me the "Compile error: Expected: end of statement" message on that line again...

I think I got it... I ran in to a new issue where the worksheet was copying to the new workbook instead of the workbook with the macro, but I got around that by changing the order of the dimensions/sets. Here is what I'm using:

Dim CopyToWbk As Workbook
Dim CashFileWbk As Workbook
Set CopyToWbk = ActiveWorkbook
Set CashFileWbk = Workbooks.Open(Sheets("Info").Range("B2").Value & "\" & Sheets("Info").Range("B3").Value)
Dim CopyFromBook As String
Dim ShToCopy As Worksheet
Set CopyFromWbk = CashFileWbk
Set ShToCopy = CopyFromWbk.Worksheets("123")

ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
Worksheets("Cash").Delete
Worksheets("123").Name =  "Cash"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Resize(, 4).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

CashFileWbk.Close

Glad to hear you found a solution!