I have a main sheet to display and be used to create/delete sheets. I have a sheet with prices for glass, aluminum and other materials, and different Template sheets for Windows/Doors/Roofs, that all pull the prices from Reference sheet.
I created code so that when I type the number of windows I need in the main sheet, I get that many copies of the Window template sheet. Then I have a few formulas that sums all sheets and gives me a total price. This way, I can go back to each sheet and edit the window configuration.
Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Cells.Count = 0 Then Exit Sub
If IsNumeric(target) And target.Address = "$B$4" Then
Select Case target.Value
Case 1 To 15: copierW
Case Is > 15: copierW
End Select
End If
End Sub
Sub copierW()
Dim sh1 As Worksheet, sh2 As Worksheet, x As Integer
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("W")
x = ActiveSheet.Range("b4")
For numtimes = 1 To x
ActiveWorkbook.Sheets("W").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
Worksheets(1).Select
End Sub
The issue is, for example, if I initially type in "8" as number of windows needed, it will create eight copies. If I change my mind and want only 6, and type in 6, it will create an additional 6 copies.
I'm looking for a way to keep the 1-6 sheets with all information already in it, and delete the excess. I tried deleting all Sheets names Window (6+) based on the name, but it would still execute the copy function first.
There's other things to do, like the main sheet displaying a certain cell from each sheet, but stack them under each-other. So A2 in Window1(sheet) would be displayed in Main sheet - C4, but A2 in Window2(sheet) would be displayed in Main Sheet - C5, etc.
In response to your comment. This will Just name them "W" and the number. In your case it didn´t work bc you didn´t change the counter function. This resulted in not counting and therefore it started to add them another time wich is why the error occured.
Sub copierW()
Dim sh1 As Worksheet, sh2 As Worksheet, sh As Worksheet, x As Integer
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("W")
x = ActiveSheet.Range("b4")
Count = 0
For Each sh In Worksheets
If Left(sh.Name, 1) = "W" and Right(sh.Name,1)="." Then
Count = Count + 1
End If
Next sh
needed_copies = x - Count
For numtimes = 1 To needed_copies
ActiveWorkbook.Sheets("W").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Count2 = Count + numtimes
ActiveSheet.Name = "W" & Count2 & "."
Next
Worksheets(1).Select
End Sub