WB to WB Copy-Paste Error

[Nurzhan]

Hallo. This is a simple copy paste task but VBA makes me crazy with errors. Where is my mistake? The task is to copy a range of cells from WBb to WBa. Both of WBs are open.

Sub GasRate()Dim WBa As Workbook
Dim WBb As Workbook
Dim lastRow As Integer


Set WBa = Workbooks("GL Rates Calculation.xlsm") 
Set WBb = Workbooks("DPR_ALS_June_2018.xlsm") 


WBb.Sheets("Gas Balance").Cells(Rows.Count, "AC").End(xlUp).Row.Resize(-15).Copy         <===Error "424", object required here.
WBa.Sheets("GL").Range("N5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End Sub

 

[Peter_SSs]

Where is my mistake?

1. WBb.Sheets("Gas Balance").Cells(Rows.Count, "AC").End(xlUp).Row is just a number, not a range so it cannot be resized.

2. Even if the first part was a range, the first argument in Resize() is how many rows the new range will contain. A range cannot contain a negative number of rows.

Can you tell us in words what range in WBb you are actually trying to copy?

 

[Nurzhan]

Thanks Peter_SSs! The idea is to to find the last row in column AC and resize it for 15 rows up then copy that range. This range location will be dymanic but in the same column, will be moving downwards on daily basis. For example cuurent range = AC10:AC25.

 

[Peter_SSs]

OK, then try this (untested at my end)

WBb.Sheets("Gas Balance").Cells(Rows.Count, "AC").End(xlUp).Offset(-14).Resize(15).Copy

 

[Nurzhan]

Thanks! now it works. I was confusing Resize with Offset, that's why used negative value))

 

[Peter_SSs]

Thanks! now it works. I was confusing Resize with Offset, that's why used negative value))

Glad we could resolve it.