Copy Paste Formulas and Format - Not just values

[jwb1012]

Hello, I am having a bit of trouble getting my code to copy/paste everything (formula and formatting) from the "Template Tasks" worksheet - opposed to just values. I think it should be a quick/easy adjustment, but I haven't been able to figure out how to modify.

Any assistance would be greatly appreciated.

Sub CopyTasks()
 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("A31:L31").Resize(sh.[L2]).Value
  End If
 Next sh
End Sub

 

[JoeMo]

Hello, I am having a bit of trouble getting my code to copy/paste everything (formula and formatting) from the "Template Tasks" worksheet - opposed to just values. I think it should be a quick/easy adjustment, but I haven't been able to figure out how to modify.

Any assistance would be greatly appreciated.

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

Your code doesn't do any copy/paste - it just sets the values on one sheet equal to those on another sheet. You can do a copy of the template sheet/range, then pastespecial to the destination sheet. See Range.pastespecial in VBA help[.

 

[jwb1012]

Any thoughts on how I can accomplish this? 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 (including formula and format) in column A, after the last non-empty row in column L. Must do this for all sheets that begin with "Labor BOE."