VBA To Copy Sheets Not Working

crburke92

Board Regular
Joined
Feb 5, 2019
Messages
71
Hi all!

So i have a workbook that has about 30 hidden 'template' sheets in it, and one worksheet open with the name of each sheet and a button The button brings up an application box which renames the copied template to what you type. The idea is I have one main workbook that I can copy into every job folder, and with ease you can build the test file for all the equipment on a specific site. I have a separate module made for each potential sheet, which is then assign to its corresponding button. The code is:

VBA Code:
Sub New_HV_CircuitBreaker_Sheet()
Dim WSName As String: WSName = Application.InputBox("Enter New SheetName")
Dim IDX As Integer: IDX = Sheets("HV CIRCUIT BREAKER_TEMP").Index

Sheets("HV CIRCUIT BREAKER_TEMP").Visible = True
    Sheets("HV CIRCUIT BREAKER_TEMP").Copy After:=Sheets(IDX)
        Sheets("HV CIRCUIT BREAKER_TEMP").Visible = Flase
            Sheets(IDX + 1).Name = WSName
End Sub

Each module pretty much looks the same:

Code:
Sub New_Three_Phase_Relay_Sheet()
Dim WSName As String: WSName = Application.InputBox("Enter New SheetName")
Dim IDX As Integer: IDX = Sheets("THREE PHASE RELAY_TEMP").Index

Sheets("THREE PHASE RELAY_TEMP").Visible = True
    Sheets("THREE PHASE RELAY_TEMP").Copy After:=Sheets(IDX)
        Sheets("THREE PHASE RELAY_TEMP").Visible = Flase
            Sheets(IDX + 1).Name = WSName
End Sub

The issue I am having is even though everything is its own module referencing different sheets, it will eventually create a copy of the sheet I want, but actually rename the previous template sheet. As in I run my HVCircuitBreaker sub...it makes a copy of the HVCircuitBreaker_Temp, and renames it based on the application box. I then try to make a ThreePhaseRelay worksheet, it copies the proper template but leave me with THREE PHASE RELAY_TEMP(2), and renames HV CIRCUIT BREAKER_TEMP to my application box input.

Is it because each module has the variable IDX? Any help is mucho appreciated!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Use
VBA Code:
            ActiveSheet.Name = WSName
Also you have a typo in both code, it should be False, not Flase ;)
 
Upvote 0
Use
VBA Code:
            ActiveSheet.Name = WSName
Also you have a typo in both code, it should be False, not Flase ;)

Haha just quickly openned a random old version, must have been pretty old to still have that typo because I found that pretty quick! And sorry, where would I actually put what you suggested? Top? Right after I Dim my Variables? I only knew enough VBA to be dangerous :P
 
Upvote 0
It needs to replace this line Sheets(IDX + 1).Name = WSName in both codes.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,729
Members
448,294
Latest member
jmjmjmjmjmjm

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