VBA code - File Path Error

[MEBAILEY]

I am having a problem with VBA code related to the file path name. In Macro1 - the files are located on the C: drive with no folders and it works. In Macro2 - the files are in folders as shown in code below and does not work.

Please help - I need someone to show me the code to allow the file path to be work in Macro2. I am not sure if the spaces or "-" in the file path name are a problem.

Thanks, Marc B.


'Macro1 - working
Sub
Workbooks("LH - COA - Current.xlsm").Worksheets("COA - final").Range("B6:K200").Copy
Workbooks.Open("C:\COA.xlsm").Worksheets("CC-COA-Expense").Range("B6").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks("COA.xlsm").Close SaveChanges:=True
End Sub


'Macro2 - not working - file path error
Sub
Workbooks("LH - COA - Current.xlsm").Worksheets("COA - final").Range("B6:K200").Copy
Workbooks ("C:\Users\Marc\Desktop\In-Rhythm\- Life House\LifeHouse - Credit Card Master\LH - CC - Templates\2 LH-CC-Master\LH - CC - Angela Capito.xlsm")
Application.CutCopyMode = False
Workbooks("C:\Users\Marc\Desktop\In-Rhythm\- Life House\LifeHouse - Credit Card Master\LH - CC - Templates\2 LH-CC-Master\LH - CC - Angela Capito.xlsm").Close SaveChanges:=True
End Sub

 

[Domenic]

You need to use the Opens method of the Workbooks collection. So in keeping with your first macro, try...

Workbooks.Open("C:\Users\Marc\Desktop\In-Rhythm\- Life House\LifeHouse - Credit Card Master\LH - CC - Templates\2 LH-CC-Master\LH - CC - Angela Capito.xlsm").Worksheets("CC-COA-Expense").Range("B6").PasteSpecial Paste:=xlPasteValues

Hope this helps!