CODEWITHSUNDEEP
vbaexcel
user3275237
user3275237

Reputation: 21

Subscript out of range error in excel

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)

Upvotes: 2

Views: 360

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

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

Upvotes: 2

Peter Albert
Peter Albert

Reputation: 17505

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

Upvotes: 0

Related Questions