How to copy and formula paste multiple tabs using a macro

[jeffroth94]

Currently I use a macro to create a workbook with everyday of the month as the title of every tab. I am able to copy the template, that I have created, for each spreadsheet but it can get annoying to have to go in and copy and formula paste every time. Here is my current formula:

‘Code Starts here

Sub Dtpopulate()
Dim S As Integer
Dim X As Integer
S = Sheets("Temp").Range("c5").Value
For X = 1 To S
newname = Sheets("Temp").Range("a6").Value
Worksheets("Template").Activate
Sheets("Template").Cells.Select
Selection.Copy


Sheets.Add.Name = newname
Sheets(newname).Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Sheets("Temp").Range("d5").Value = Sheets("Temp").Range("b5") + X
Next X

End Sub

Can I add more to this formula to be able to copy and paste formula?

 

[svendiamond]

Can I add more to this formula to be able to copy and paste formula?

What exactly do you want to copy and paste? What cells, from where and to where? It looks like your code is already pasting values and formats...

This should do the same exact thing your code does, except it will copy everything rather than just the values/formats:

Sub Dtpopulate()
Dim S As Integer, X As Integer


S = Sheets("Temp").Range("C5").Value


For X = 1 To S
    newname = Sheets("Temp").Range("A6").Value
    Worksheets("Template").Sheets("Template").Cells.Copy _
        Sheets(newname).Range("A1")
        
    Sheets.Add.Name = newname
    Sheets("Temp").Range("D5").Value = Sheets("Temp").Range("B5") + X
Next X


End Sub

 

[jeffroth94]

I want to copy and paste the formulas from the template. Right, it does copy and paste the values and the format but it does not copy the formulas in cells C9:K57. Svendiamond, your formula is giving me an error that the subscript is out of range. Do I need to add to the formula?

 

[svendiamond]

When you click "Debug" what line is highlighted in yellow? Actually I think I see the problem. Try this:

Sub Dtpopulate()Dim S As Integer, X As Integer


S = Sheets("Temp").Range("C5").Value


For X = 1 To S
    newname = Sheets("Temp").Range("A6").Value
    Sheets.Add.Name = newname
    Worksheets("Template").Sheets("Template").Cells.Copy _
        Sheets(newname).Range("A1")


    Sheets("Temp").Range("D5").Value = Sheets("Temp").Range("B5") + X
Next X


End Sub

 

[jeffroth94]

I figured out what I needed to add. Thank you for the help!