how to use range with variables - 1004 error keeps surfacing

0
votes
Dim lastrow&, lastCol&, myarray As Range

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Range("XX1").End(xlToLeft).Column
Set myarray = Range("A1").Resize(lastrow, lastCol)



Application.CutCopyMode = False

Worksheets("Sheet1").Range("A1", myarray).Copy



Application.WindowState = xlNormal
Windows("macrofile.xlsm").Activate
Sheets("MRG").Select
'has to find the last row by itself
Range("A" & Rows.Count).End(xlUp).Offset(2, 0).Select


ActiveCell.PasteSpecial (xlPasteValues)

Hi, been running this simple code to copy and paste selected cells and I keep getting run-time error 1004: "application-defined or object-defined error".

The error triggers on "Worksheets("Sheet1").Range("A1", myarray).Copy". I surmise it is due to the "myarray" variable. I have tried splitting it up to sheets("sheet1").select and range("a1", myarray).select then selection.copy, but I just get another error 1004: method 'range' of object'_global' failed.

Any help is much appreciated. I'm at a loss.

1
excelvba
Worksheets("Sheet1").Range(myarray).Copy should work. – Variatus

1 Answers

1
votes

You need to specify the sheet, then those errors won't occure. Like this:

Dim lastrow&, lastCol&, myarray As Range
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

With ws
   lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
   lastCol = .Range("XX1").End(xlToLeft).Column
   Set myarray = .Range("A1").Resize(lastrow, lastCol)



   Application.CutCopyMode = False

   .Range("A1", myarray).Copy
End With
...