Copying Cells from Workbook to Workbook Results in "Subscript Out of Range"

Question

Good day.

I am trying to write a code that will essentially select cells (left to right) from "A - M" and (downwards) up until the last used row.
Then, once selected, I was to copy them to another workbook.

This is the code that I tried to use:

ActiveWorkbook.Worksheets("Sheet1").Range("A1:M" & LastRow).Copy _
    Workbooks("Converter.xlsm").Worksheets("Sheet1").Range("A1").CurrentRegion

Everything else in the code works except for this line.
And the result is this:

Run-time error '9':

Subscript out of range

Thanks.

PS. The "LastRow" Variable I used, I just pulled that out of a tutorial. That's not a personal user-defined variable, so I am not sure if that's actually from VBA's Documentation.

try take away `.CurrentRegion` for the destination cells – Rosetta Apr 22 '16 at 02:22 — Rosetta, Apr 22 '16 at 02:22

score 0 · Accepted Answer · edited May 23 '17 at 11:45

0

You mentioned you do not have a calculation for LastRow.

Always put Option Explicit at the top of your module. It forces you to declare your variables and will help you recognize if something you got from another coder is "built-in" or not.
See this post about determining the last row in a range.
From that post, you should have a calculation such as the following before your line of code ...
```
LastRow = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
```

edited May 23 '17 at 11:45

Community

1
1

answered Apr 22 '16 at 04:22

OldUgly

2,129
3
13
21

Ah... I see. So, 'Option Explicit' will be an automatic aid. Thank you. But I have to ask, though. What is 'xlUp' ? – TheBSITGuy Apr 22 '16 at 08:22
xlUp is a constant, built in functionality of VBA for Excel. – OldUgly Apr 22 '16 at 13:57

score 0 · Answer 2 · answered Apr 22 '16 at 04:33

Please close all the workbooks and reopen and then try with below code

Sub test()
    Dim lastrow As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveWorkbook.Worksheets("Sheet1").Range("A1:M" & lastrow).Copy Workbooks("Converter.xlsm").Worksheets("Sheet1").Range("A1")
End Sub

Copying Cells from Workbook to Workbook Results in "Subscript Out of Range"

2 Answers2