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.

 

[leever]

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

Hi there, thank you again. Same issue is happening where the [Template] values are pasted in the other sheets [Toronto], [Ottawa]. [Vancouver], [Montreal], [Calgary].

Any further help would be appreciated.

 

[Akuini]

Hi there, thank you again. Same issue is happening where the [Template] values are pasted in the other sheets [Toronto], [Ottawa]. [Vancouver], [Montreal], [Calgary].

Any further help would be appreciated.

Try this one:

Sub CopySheet_1()
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.Name = Sheets("Cities").Cells(i, 1)
    ActiveSheet.UsedRange.Formula = Sheets("Template").UsedRange.Formula
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
Next i

End Sub

 

[leever]

Try this one:

Sub CopySheet_1()
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.Name = Sheets("Cities").Cells(i, 1)
    ActiveSheet.UsedRange.Formula = Sheets("Template").UsedRange.Formula
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
Next i

End Sub

Hello Akuini! This worked perfectly! Many many thanks!

 

[Akuini]

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