Workbook won't close using VBA

[stephen antoine]

Hello all,

For some reason the code below gets an error saying the subscript is out of range once it gets to the last step...

CM_Current_Payments_Export = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\05 - Jun\Export_Current_Payments_Active_Jun-2008.xls"
 
Workbooks.Open Filename:= _
        CM_Current_Payments_Export
    Cells.Copy
    Windows(CM_PP_Rec).Activate
    Sheets("Current_Payments").Paste
Workbooks(CM_Current_Payments_Export).Close savechanges:=False

Does anyone know why the error is popping up when the sub tries to close the workbook?

 

[Jonmo1]

Workbooks.Open - Requires Path and Filename
Workbooks(book).Close - DOES NOT use Path...
so you should separate your variables into 2
1st for path
2nd for filename

Example..

MyPath ="J:\Utilities\Building Rent\FY2008\Rent Reconciliation\05 - Jun\"

CM_Current_Payments_Export = "Export_Current_Payments_Active_Jun-2008.xls"
 
Workbooks.Open Filename:= _
        MyPath & CM_Current_Payments_Export
    Cells.Copy
    Windows(CM_PP_Rec).Activate
    Sheets("Current_Payments").Paste
Workbooks(CM_Current_Payments_Export).Close savechanges:=False

Hope that helps..

 

[stephen antoine]

That worked!!! Sweet!! Thanks for your help...

 

[Norie]

Stephen

One way you could have avoided this would be to create a reference to the workbook when you open it.

Dim wbOpen As Workbook
    CM_Current_Payments_Export = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\05 - Jun\Export_Current_Payments_Active_Jun-2008.xls"
 
    Set wbOpen = Workbooks.Open(Filename:=CM_Current_Payments_Export)
    With wbOpen
        .ActiveSheet.Cells.Copy Workbooks(CM_PP_Rec).Sheets("Current_Payments").Range("A1")
        .Close.Close savechanges:=False
    End With

 

[stephen antoine]

Thanks Norie