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]

It works for me.

What is the value of Ary(i) when you get the error & do you have a sheet with that name in WBb?
Also do you have sheet protection in WBb or merged cells?

 

[Nurzhan]

Yes, there's such sheet and the preceding steps are running fine but it stops at that line. There are merged cells but they shouldn't affect those columns I want to add formulas. I disabled that problem line to see if the next lines are ok but they encounter same problems.

 

[Fluff]

What if you try

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

 

[Nurzhan]

I tried it as well. No success.

 

[Fluff]

In that case I'm afraid I don't know what to suggest, as that line works for me.

 

[Nurzhan]

Dear Fluff, maybe you can look at those files if I share with you? I dunno what's going on there, I tried everything, doesn't help.

 

[Fluff]

I'll happily take a look, if you can upload the file to a share site such as OneDrive, Dropbox, googledrive, mark for sharing & post the link to this thread.

 

[Nurzhan]

Here are the links:
https://www.dropbox.com/s/anx0bx80r3uynrs/SWDP%20June%202010%20-%20April%202018%20%28Gas-Condensate%20wells%29.xlsx?dl=0
https://www.dropbox.com/s/z11j06kgdqdt54x/Single Well Daily Profile - May 2018.xlsm?dl=0

I tried to use "Autofill" instead of inserting formulas. But It didn't work as well.

 

[Fluff]

Ok, try this

    For i = 0 To UBound(Ary)
      lastRowB = WBa.Worksheets(Ary(i)).Cells(Rows.Count, "B").End(xlUp).Row
      
      WBa.Sheets(Ary(i)).Range("K:L,R:S").Delete Shift:=xlToLeft 'deletes not-needed columns
      WBa.Sheets(Ary(i)).Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 'inserts a column for CGR in g/m3
      WBa.Sheets(Ary(i)).Range("B9:AC" & lastRowB).Copy 'copies range from Single well daily profile
      
      
      WBb.Sheets(Ary(i)).Cells(Rows.Count, "B").End(xlUp).Offset(1).Insert Shift:=xlShiftDown
      WBb.Sheets(Ary(i)).Cells(Rows.Count, "F").End(xlUp).Offset(1).Resize(lastRowB).FormulaR1C1 = "=IF(RC[4]=0,"" - "",RC[-1]*RC[20]/1000)"  'It stops running here'
      WBb.Sheets(Ary(i)).Cells(Rows.Count, "AD").End(xlUp).Offset(-50).Resize(50).FormulaR1C1 = "=RC[-14]/1000"
      WBb.Sheets(Ary(i)).Cells(Rows.Count, "I").End(xlUp).Resize(, 3).FormulaR1C1 = "=SUM(R9C:R[-1]C)"
     [COLOR=#0000ff] WBb.Sheets(Ary(i)).Union(Range("M" & lastRow + 1), Range("N" & lastRow + 1), Range("P" & lastRow + 1)).Value = "=SUM(R9C:R[-1]C)"
      WBb.Sheets(Ary(i)).Union(Range("O" & lastRow + 1), Range("Q" & lastRow + 1)).Value = "=RC[-1]/RC[-6]"[/COLOR]
    Next i

You'll need to split the 2 lines in blue into multiple lines, as I don't think you can do that with non contiguous cells

 

[Nurzhan]

Error 438 at

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