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

leever

New Member
Joined
Jun 29, 2023
Messages
9
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.
 
Sorry, wrong tag. It should be:
Rich (BB 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)
    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.
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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:
VBA Code:
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
 
Upvote 0
Solution
Try this one:
VBA Code:
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!
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,224,557
Messages
6,179,504
Members
452,917
Latest member
MrsMSalt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top