I am getting Runtime error 1004 when using Range class for creating a macro in VBA

Question

I am trying to automate one report in Excel. I started with creating macro. At one point in macro I am copying one row from AZ2 to CJ2 using variables, each cell has formulas and I want to paste the same formulas from AZ3 to AZ248000. I am getting error for this code Wb1.Sheets("Gains Data").Range(Cells(row3, Col1), Cells(Row2, Col1)).Select

I already tried number of examples and solutions provided online, I started with autofill, also tried to just pasting formula. I tried to activate file, sheet, worksheet as suggested for the similar problems online. Nothing worked. Please check my code and advise the solution. PS - before this code there is bunch of code where I am copying data from different workbook Wb2 onto the main one Wb1.

   Dim Row1 As Long
   Dim Row2 As Long
   Dim row3 As Long
   Dim Col1 As String
   Dim Col2 As String

   Wb1.Worksheets("Gains Data").Range("AY2").Select
   Selection.End(xlDown).Select
   Row2 = ActiveCell.Row

 ' Wb1.Worksheets("Gains Data").Range("AZ2").Select
       ' Selection.End(xlToRight).Select
    ' Col2 = ActiveCell.Column


Wb1.Worksheets("Gains Data").Range("AZ2").Select
Row1 = ActiveCell.Row
row3 = Row1 + 1
Col1 = ActiveCell.Column
Wb1.Worksheets("Gains Data").Range(Selection, 
Selection.End(xlToRight)).Select
'Col2 = ActiveCell.Column
Selection.Copy
'Windows("Gains SOP Forecast Comparison 2019 07.xlsb").Activate
 Wb1.Sheets("Gains Data").Activate

'Range("O1:X1").Select

'Selection.AutoFill Destination:=Range(Col1 & Row1 & ":" & Col2 & Row2), 
  Type:=xlFillDefault

'Range("BA3:B" & LastRow1 & ":" & "CJ3:CJ" & LastRow1).Formula=

 'Cells(Col1 & Row1 & ":" & Col2 & Row2).Paste
 'Sheets("Gains Data").Range(Col1 & row3 & ":" & Col1 & Row2).Select
 Wb1.Sheets("Gains Data").Range(Cells(row3, Col1), Cells(Row2, 
 Col1)).Select
  ActiveSheet.Paste  ' Copies the formulas from BA to CJ for all rows

The formulas should be copied down to all the rows depending on number of rows Wb2 has which I am storing in the row2 variable.

How to avoid using Select in VBA will help you solve this issue. — Ken White
You need to qualify the Worksheet the Cells are on. Otherwise ActiveSheet is implied, which may not be Wb1.Sheets("Gains Data"). Using a With...End With block will make this easier. — BigBen
Thanks I am trying This Wb1.Sheets("Gains Data").Activate With Ws1 Dim rng1 As Range Dim rng2 As Range Set rng1 = Wb1.Worksheets("Gains Data").Range(Col1 & Row1 & ":" & Col2 & Row1) Set rng2 = Wb1.Worksheets("Gains Data").Range(Col1 & Row3 & ":" & Col1 & Row2) rng1.Copy rng2 End With -----------------Now Row2 has got value of 248500 I am still getting error 1004 for rng2 is it because of Row2 number is so high? — Jimmy
Dim Col1 as Long, not As String. Same for Col2. Then use Cells instead of Range. — BigBen

TedZheng TedZheng · Accepted Answer · 2019-07-10T01:29:18

If you need copy the data from one workbook to another, please try below code:

Sub Copy_Between_Workbook()
Dim wb_path$
Dim wb1, wb2
Dim i, j

wb_path = ThisWorkbook.Path    'Path of workbook
myfile1 = "workbook1.xlsx"
myfile2 = "workbook2.xlsx"
mysheet = "Sheet1"
'Original workbook
Set wb1 = CreateObject(wb_path & "\" & myfile1)
'Target workbook
Set wb2 = CreateObject(wb_path & "\" & myfile2)
'attend: wb1.sheet1 is not work, must be wb1.sheets("xxx")
wb1.Sheets(mysheet).UsedRange.Copy wb2.Sheets(mysheet).Range("A1")

wb1.Close False
wb2.Close True

'Out cut mode
Application.CutCopyMode = False
Debug.Print Timer
End Sub

I am getting Runtime error 1004 when using Range class for creating a macro in VBA

1 Answers