VBA Error: Runtime Error: 9 - Subscript out of range

Question

Requirement: I need to copy 1 Column - Col G (need to determine the number of rows dynamically) from 1 Workbook to another.

Problem: Getting VBA Error:

Runtime Error: 9 - Subscript out of range Please help.

Sub Set_Open_ExistingWorkbook()

Dim wkb As Workbook

Set wkb = Workbooks.Open("C:\Users\me364167\Documents\Practice_OB_Status_Detailed_Report_Mainframe.xls")
'Set wkb = Workbooks.Open("C:\Users\me364167\Documents\Practice level_Opportunity Pipeline_Mainframe.xls")

Dim LastRow As Long
Dim Sheet1Data As Long

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With

Workbooks("Practice_OB_Status_Detailed_Report_Mainframe.xls").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "G"), Cells(LastRow, "G")).Copy
Workbooks("OB Macro.xlsx").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "A"), Cells(LastRow, "A")).PasteSpecial


' Workbooks("Practice_OB_Status_Detailed_Report_Mainframe.xls").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "G"), Cells(LastRow, "G")).Copy
' Workbooks("OB Macro.xlsx").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "A"), Cells(LastRow, "A")).PasteSpecial Paste:=xlValues

wkb.Close

End Sub

try not to use ActiveSheet when searching for you LastRow, instead use referenced objects, like With wkb.Worksheets("OB_Status_Detailed_Report"). You are missing one parameter after the line with PasteSpecial , you need to add something like PasteSpecial xlValues — Shai Rado
With wkb.Worksheets("OB_Status_Detailed_Report") LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row End With Workbooks("Practice_OB_Status_Detailed_Report_Mainframe.xls").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "G"), Cells(LastRow, "G")).Copy Workbooks("OB Macro.xlsx").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "A"), Cells(LastRow, "A")).PasteSpecial xlPasteValues — meru
Workbooks("OB Macro.xlsx").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "A"), Cells(LastRow, "A")).PasteSpecial xlPasteValues — meru

SJR SJR · Accepted Answer · 2016-11-17T12:48:14

This perhaps? Your ranges are not fully qualified and as Shai Rado says you are missing a PS paremeter.

Sub Set_Open_ExistingWorkbook()

Dim wkb As Workbook

Set wkb = Workbooks.Open("C:\Users\me364167\Documents\Practice_OB_Status_Detailed_Report_Mainframe.xls")
'Set wkb = Workbooks.Open("C:\Users\me364167\Documents\Practice level_Opportunity Pipeline_Mainframe.xls")

Dim LastRow As Long
Dim Sheet1Data As Long

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With

With wkb.Worksheets("OB_Status_Detailed_Report")
    .Range(.Cells(1, "G"), .Cells(LastRow, "G")).Copy
End With

With Workbooks("OB Macro.xlsx").Worksheets("OB_Status_Detailed_Report")
    .Range(.Cells(1, "A"), .Cells(LastRow, "A")).PasteSpecial xlValues
End With

' Workbooks("Practice_OB_Status_Detailed_Report_Mainframe.xls").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "G"), Cells(LastRow, "G")).Copy
' Workbooks("OB Macro.xlsx").Worksheets("OB_Status_Detailed_Report").Range(Cells(1, "A"), Cells(LastRow, "A")).PasteSpecial Paste:=xlValues

wkb.Close

End Sub

VBA Error: Runtime Error: 9 - Subscript out of range

1 Answers