Copy varying number of rows from one sheet to another depending on cell reference

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hi - I am attempting to come up with a code to copy a varying number of rows from a "Template" worksheet to multiple sheets in my workbook depending on a cell reference on each sheet.

So, for each sheet in the workbook that begins with "Labor BOE", look at the number in cell "L2", copy that many rows from the "Template" worksheet(columns A:L beginning in cell "A21"), and paste them in column A, after the last non-empty row in column L. Must do this for all sheets that begin with "Labor BOE."

Any thoughts on how I can accomplish this?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
610
Office Version
  1. 365
Platform
  1. Windows
Hi. Try:
Code:
Sub CopyPasteData()
 Dim ws As Worksheet
 For Each ws In ThisWorkbook.Worksheets
  If ws.Name Like "LABOR BOE*" And ws.[L2] > 0 Then
   ws.Range("A" & ws.Cells(Rows.Count, 12).End(3)(2).Row).Resize(ws.[L2], 12).Value = _
    Sheets("Template").Range("A21:L21").Resize(ws.[L2]).Value
  End If
 Next ws
End Sub
 

Rhodie72

Well-known Member
Joined
Apr 18, 2016
Messages
546
Hi - I am attempting to come up with a code to copy a varying number of rows from a "Template" worksheet to multiple sheets in my workbook depending on a cell reference on each sheet.

So, for each sheet in the workbook that begins with "Labor BOE", look at the number in cell "L2", copy that many rows from the "Template" worksheet(columns A:L beginning in cell "A21"), and paste them in column A, after the last non-empty row in column L. Must do this for all sheets that begin with "Labor BOE."

Any thoughts on how I can accomplish this?


If I've understood this then this bit of code might be the starting point for you:
Code:
Sub L2_Problem()
'   Must do this for all sheets that begin with "Labor BOE." in the workbook that begins with "Labor BOE", _
    look at the number in cell "L2", _
    copy that many rows from the "Template" worksheet(columns A:L beginning in cell "A21"), _
    and paste them in column A, after the last non-empty row in column L.

    Dim GotIt As Long

    For i = 1 To ActiveWorkbook.Sheets.Count
        sheet(i).Activate
        Select Case ActiveSheet.Name
            Case "Labor BOE"
                GotIt = [L2].Value
                If GotIt > 0 Then
                    Sheets("Template").Columns("A21:L").Copy Range("A1").End(xlDown).Offset(1, 0) ' beginning in cell "A21"
                End If
            Case Else
        End Select
    Next i

End Sub
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Modified the code slightly, but same structure used. Results in Run TIme Error 424: Object Required

Code:
Sub CopyPasteData()
 Dim sh As Worksheet
 For Each sh In ThisWorkbook.Worksheets
  If Left(sh.name, 9) = "Labor BOE" Then
   sh.Range("A" & sh.Cells(Rows.Count, 12).End(3)(2).Row).Resize(ws.[L2], 12).Value = _
    Sheets("Template - Tasks").Range("A21:L21").Resize(sh.[L2]).Value
  End If
 Next sh
End Sub
 

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167

ADVERTISEMENT

@Rhodie74 -- It results in a compile error. Compile Error: Sub or Function Not Defined

Code:
Sub L2_Problem()
'   Must do this for all sheets that begin with "Labor BOE." in the workbook that begins with "Labor BOE", _
    look at the number in cell "L2", _
    copy that many rows from the "Template" worksheet(columns A:L beginning in cell "A21"), _
    and paste them in column A, after the last non-empty row in column L.

    Dim GotIt As Long

    For i = 1 To ActiveWorkbook.Sheets.Count
        sheet(i).Activate
        Select Case ActiveSheet.Name
            Case "Labor BOE"
                GotIt = [L2].Value
                If GotIt > 0 Then
                    Sheets("Template").Columns("A21:L").Copy Range("A1").End(xlDown).Offset(1, 0) ' beginning in cell "A21"
                End If
            Case Else
        End Select
    Next i

End Sub
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
610
Office Version
  1. 365
Platform
  1. Windows
Modified the code slightly, but same structure used. Results in Run TIme Error 424: Object Required

Code:
sh.Range("A" & sh.Cells(Rows.Count, 12).End(3)(2).Row).Resize([COLOR=#ff0000][B]sh[/B][/COLOR].[L2], 12).Value = _

That red variable should be replaced as well
 

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167

ADVERTISEMENT

Hi Osvaldo - thank you for the reply! For some reason, it is pasting the data beginning in cell D4, instead of cell D21. Also, it is not pasting the formatting or formulas. Any thoughts?

Code:
Sub CopyPasteData()
 Dim sh As Worksheet
 For Each sh In ThisWorkbook.Worksheets
  If Left(sh.name, 9) = "Labor BOE" Then
   sh.Range("A" & sh.Cells(Rows.Count, 12).End(3)(2).Row).Resize(sh.[L2], 12).Value = _
    Sheets("Template - Tasks").Range("A21:L21").Resize(sh.[L2]).Value
  End If
 Next sh
End Sub
 

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
CORRECTION: I made an adjustment to my spreadsheet and it is pasting in the correct location but, it is not copying the formulas or formatting. Thoughts?
 

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hi Osvaldo - I now understand that this code is simply setting the cells on the Labor BOE worksheet equal to the Template worksheet. This will not work because there are formulas doing lookups that need to be copy/pasted onto the Labor BOE worksheet from the Template worksheet.

Is it possible to adjust the logic within the code to copy/paste the range from the template onto the various sheets, including format and formula?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top