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

[jwb1012]

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?

 

[Osvaldo Palmeiro]

Hi. Try:

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]

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:

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

 

[jwb1012]

Modified the code slightly, but same structure used. Results in Run TIme Error 424: Object Required

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]

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

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]

Modified the code slightly, but same structure used. Results in Run TIme Error 424: Object Required

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]

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?

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]

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]

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?