VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed

[manuvizcarra]

Hello MrExcel community, I´m new to VBA and I´d like to know if someone knows what´s wrong with my code.

The code should copy a range from workbook "ReporteCNG.xlsx" and paste it in workbook "Consumos CNG1.xlsx".

However, when I run into an error in the line where I try to copy the range from Workbook "ReporteCNG.xlsx") Worksheet("Sheet1")

Sub Copy_Paste_CNG()

Workbooks.Open "C:\Users\manuv\Documents\PELSA\Consumos CNG1.xlsx"

Dim fila As Long
Dim cng As Worksheet
Dim desc As Worksheet
Dim prueba As Range

fila = Cells(Rows.Count, 1).End(xlUp).Row
Set cng = Workbooks("Consumos CNG1.xlsx").Worksheets("General")
Set desc = Workbooks("ReporteCNG.xlsx").Worksheets("Sheet1")
Set prueba = Workbooks("Consumos CNG1.xlsx").Worksheets("Hoja1").Range("A1")

desc.Range(Cells(2, 1), Cells(fila, 18)).Copy prueba

Workbooks("Consumos CNG1.xlsx").Close SaveChanges:=True

End Sub[/CODE]

Thank you for your help!

 

[manuvizcarra]

Hello MrExcel community, I´m new to VBA and I´d like to know if someone knows what´s wrong with my code.

The code should copy a range from workbook "ReporteCNG.xlsx" and paste it in workbook "Consumos CNG1.xlsx".

However, when I run into an error in the line where I try to copy the range from Workbook "ReporteCNG.xlsx") Worksheet("Sheet1")

Sub Copy_Paste_CNG()

Workbooks.Open "C:\Users\manuv\Documents\PELSA\Consumos CNG1.xlsx"

Dim fila As Long
Dim desc As Worksheet
Dim prueba As Range

fila = Cells(Rows.Count, 1).End(xlUp).Row
Set desc = Workbooks("ReporteCNG.xlsx").Worksheets("Sheet1")
Set prueba = Workbooks("Consumos CNG1.xlsx").Worksheets("Hoja1").Range("A1")

desc.Range(Cells(2, 1), Cells(fila, 18)).Copy prueba
View attachment 40792
Workbooks("Consumos CNG1.xlsx").Close SaveChanges:=True

End Sub[/CODE]

Thank you for your help!

 

[Marc L]

Hi,​

​

do not forget to add the worksheet reference to each Cells statement of this codeline …​

 

[manuvizcarra]

Hi,​

​

do not forget to add the worksheet reference to each Cells statement of this codeline …​

Thank you, problem solved!

 

[Marc L]

Can be avoided using With block statement rather than a worksheet variable …​