Copying a range from multiple sheets and inserting into multiple sheets in another workbook.

[Nurzhan]

Hallo,
I have 2 workbooks. A and B. A = source, B = Target. Each of them has sheets with the same names. The task is to copy a certain range from A and insert it in a cell below the last occupied one. My code is as follows but it fails to work and I couldn't troubleshoot. could you pls help.

Error appears at

lastRow = WBb.Sheets(sh.Name).Cells(Rows.Count, "B").End(xlUp).row+1

. Error = "91", saying object variable or with block variable no set.

Sub FromA2B()    
    Dim WBa As Workbook
    Dim WBb As Workbook
    Dim sh As Worksheet
    Dim lastRow As Range
    
    Set WBa = ThisWorkbook
    Set WBb = Workbooks("SWDP June 2010 - May 2017 (Oil and WI wells).xlsx")
    
    For Each sh In WBa.Worksheets
        sh.Range("B9:Z39").Copy
        lastRow = WBb.Sheets(sh.Name).Cells(Rows.Count, "B").End(xlUp).row+1
        lastRow.Select
        Selection.Insert Shift:=xlDown
        Application.CutCopyMode = False
    Next sh
End Sub

 

[Fluff]

Did you read what I said in post#29 below the code?

 

[Nurzhan]

Sorry! Yes now it looks like as follows:

WBb.Sheets(Ary(i)).Range("O" & lastRowB + 1).FormulaR1C1 = "=RC[-1]/RC[-6]"      WBb.Sheets(Ary(i)).Range("Q" & lastRowB + 1).FormulaR1C1 = "=RC[-1]/RC[-6]"
      WBb.Sheets(Ary(i)).Range("M" & lastRowB + 1).FormulaR1C1 = "=SUM(R9C:R[-1]C)"
      WBb.Sheets(Ary(i)).Range("P" & lastRowB + 1).FormulaR1C1 = "=SUM(R9C:R[-1]C)"
      WBb.Sheets(Ary(i)).Range("N" & lastRowB + 1).FormulaR1C1 = "=SUM(R9C:R[-1]C)"

but last 3 lines didn't insert formula into required cells.
and these lines insert formulas into cells lastRowB + 7 rows.

WBb.Sheets(Ary(i)).Cells(Rows.Count, "F").End(xlUp).Resize(lastRowB).FormulaR1C1 = "=IF(RC[4]=0,"" - "",RC[-1]*RC[20]/1000)" 
      WBb.Sheets(Ary(i)).Cells(Rows.Count, "AD").End(xlUp).Resize(lastRowB).FormulaR1C1 = "=RC[-14]/1000"

 

[Fluff]

Because you are only copying data from row 9 downwards you'll need to account for that like

      WBb.Sheets(Ary(i)).Cells(Rows.Count, "F").End(xlUp).Offset(1).Resize([COLOR=#ff0000]lastRowB - 8[/COLOR]).FormulaR1C1 = "=IF(RC[4]=0,"" - "",RC[-1]*RC[20]/1000)"

The other lines will put the formulas into row 40 on your test data & lastRowB is 39. You need to calculate the last row on WBb

 

[Nurzhan]

Thanks Fluff! Greatly appreciated! Now it's working perfect! I thought "lastRowB" is applicable to all the workbooks and supposed to calculate the last row in whatever sheet because it wasn't assigned to a specific sheet. isn't it so?

 

[Fluff]

Glad to help & thanks for the feedback.

LastrowB is finding the last row in WBa, which is not the same as the last row in WBb