Subscript out of range error in excel

Question

Please assist with the code below, it gives an error of "subscript out of range" when I try running the code". When i debug, it highlight the portion below. Thanks in advance.

Workbooks.Open Filename:=Path & Filename
Columns("A:AI").Copy
Windows("RealTime-APR").Activate
Sheets("Raw").Select
ActiveCell.PasteSpecial (xlPasteValues)

1) check whether you really have workbook with name "RealTime-APR" opened, 2) if yes, check whether your workbook "RealTime-APR" contains sheet with name "Raw" — Dmitry Pavliv, Feb 05 '14 at 13:04

score 2 · Answer 1 · edited May 23 '17 at 11:56

There is no need to use .Activate (INTERESTING READ) OR .Copy/.PasteSpecial xlPasteValues

Try this

I am assuming that you have declared and set Path & Filename

Dim thisWb As Workbook, newWB As Workbook
Dim thisWs As Worksheet, newWs As Worksheet

Set thisWb = ThisWorkbook
Set thisWs = thisWb.Sheets("Sheet1")

Set newWB = Workbooks.Open(Path & Filename)
Set newWs = newWB.Sheets("RAW")

newWs.Columns("A:AI").Value = thisWs.Columns("A:AI").Value

score 0 · Answer 2 · answered Feb 05 '14 at 13:09

Try this code:

Dim wbTarget as Workbook
ThisWorkbook.Columns("A:AI").Copy
Workbooks.Open Filename:=Path & Filename
Set wbTarget = Workbooks(Workbooks.Count)
wbTarget.Activate
wbTarget.Sheets("Raw").Activate
wbTarget.Sheets("Raw").Range("YourTargetColumn e.g. A:A here").PasteSpecial xlPasteValues

alternatively, use the With statement:

ThisWorkbook.Columns("A:AI").Copy
Workbooks.Open Filename:=Path & Filename
With Workbooks(Workbooks.Count)
    .Activate
    .Sheets("Raw").Activate
    .Sheets("Raw").Range("YourTargetColumn e.g. A:A here").PasteSpecial xlPasteValues
End With

Subscript out of range error in excel

2 Answers2