After copying sheet and renaming them, removing formulas and keeping values for ONLY newly created sheets

[leever]

Hello, I have a workbook with two worksheets.

Worksheet 1 is [Template] – contains formulas
Worksheet 2 is [Cities] – contains list of cities in column A (Toronto, Vancouver, Montreal, Ottawa, Calgary)

With my code, I have successfully copied the template and renamed the sheets using the list from [Cities]. I am now trying to remove the formulas and keep the values for only the new sheets.

After I run the code, this is what occurs.
[Template] – formulas removed
[Cities] – N/A
[Toronto] – formulas stay
[Ottawa], [Vancouver], [Montreal], [Calgary] – formulas removed

What I want:
[Template] – formulas stay
[Cities] – N/A
[Toronto] – formulas removed
[Ottawa], [Vancouver], [Montreal], [Calgary] – formulas removed

This is my code.
---
Sub CopySheet()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Cities").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets("Template").UsedRange.Value = Sheets("Template").UsedRange.Value
ActiveSheet.Name = Sheets("Cities").Cells(i, 1)

Next i
End Sub
---

Any help would be much appreciated. Thank you.

 

[Akuini]

Try:

Sub CopySheet()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Cities").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.UsedRange.Value = Sheets("Template").UsedRange.Value
    ActiveSheet.Name = Sheets("Cities").Cells(i, 1)

Next i
End Sub

 

[leever]

Try:

Sub CopySheet()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Cities").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.UsedRange.Value = Sheets("Template").UsedRange.Value
    ActiveSheet.Name = Sheets("Cities").Cells(i, 1)

Next i
End Sub

This worked! Thank you so much!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[leever]

Hello. I spoke too soon. What ends up happening with the code above, the [Template] values get pasted into the new sheets - [Toronto], [Ottawa], [Vancouver], [Montreal], [Calgary].
The [Template] sheet has formulas that are tied to the sheet name. After the [Toronto], [Ottawa], [Vancouver], [Montreal], [Calgary] sheets are created. I want these formulas removed and sheet values pasted. Any further assistance would be much appreciated. Thank you!

 

[Akuini]

I want these formulas removed and sheet values pasted. Any further assistance would be much appreciated. Thank you!

Do you mean you want to remove formulas in sheet Template & keep the values?

Sub CopySheet()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Cities").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.UsedRange.Value = Sheets("Template").UsedRange.Value
    ActiveSheet.Name = Sheets("Cities").Cells(i, 1)

Next i
Sheets("Template").UsedRange.Value = Sheets("Template").UsedRange.Value
End Sub

 

[leever]

Hi there, thank you again for your help with this.

For [Template], I want to keep the formulas and keep values. In this sheet, I have formulas that are calculated based on the sheet name.
For example, when [Toronto] sheet is created, it will make calculations for [Toronto] results only because sheet name is [Toronto].

With the code you provided this is what is happening:
[Template] – formulas removed and [Template] values pasted
[Cities] – N/A
[Toronto] – formulas removed and [Template] values pasted
[Ottawa] – formulas removed and [Template] values pasted
[Vancouver] – formulas removed and [Template] values pasted
[Montreal] – formulas removed and [Template] values pasted
[Calgary] – formulas removed and [Template] values pasted

This is what I am looking for:
[Template] – formulas stay and [Template] values kept
[Cities] – N/A
[Toronto] – formulas removed and [Toronto] values pasted
[Ottawa] – formulas removed and [Ottawa] values pasted
[Vancouver] – formulas removed and [Vancouver] values pasted
[Montreal] – formulas removed and [Montreal] values pasted
[Calgary] – formulas removed and [Calgary] values pasted

Thank you again.

 

[Akuini]

This is what I am looking for:
[Template] – formulas stay and [Template] values kept

Sorry for the late reply, try:

Sub CopySheet()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Cities").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    [B]ActiveSheet[/B].UsedRange.Value = Sheets("Template").UsedRange.Value
    ActiveSheet.Name = Sheets("Cities").Cells(i, 1)

Next i

End Sub

 

[leever]

Sorry for the late reply, try:

Sub CopySheet()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Cities").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    [B]ActiveSheet[/B].UsedRange.Value = Sheets("Template").UsedRange.Value
    ActiveSheet.Name = Sheets("Cities").Cells(i, 1)

Next i

End Sub

Hello, Thank you again for your help.

Using the code above, the " [B ] ActiveSheet [ / B].UsedRange.Value = Sheets("Template").UsedRange.Value "

shows as red and I get a "Compile error: Syntax error" message.

Any further help would be greatly appreciated. Thank you.

Erica

 

[Akuini]

Using the code above, the " [B ] ActiveSheet [ / B].UsedRange.Value = Sheets("Template").UsedRange.Value "

shows as red and I get a "Compile error: Syntax error" message.

Sorry, wrong tag. It should be:

Sub CopySheet()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Cities").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.UsedRange.Value = Sheets("Template").UsedRange.Value
    ActiveSheet.Name = Sheets("Cities").Cells(i, 1)

Next i

End Sub