VBA To Copy Sheets Not Working

crburke92

New Member
Joined
Feb 5, 2019
Messages
45
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!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
Use
VBA Code:
            ActiveSheet.Name = WSName
Also you have a typo in both code, it should be False, not Flase ;)
 

crburke92

New Member
Joined
Feb 5, 2019
Messages
45
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
It needs to replace this line Sheets(IDX + 1).Name = WSName in both codes.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,752
Messages
5,597,921
Members
414,190
Latest member
PuzzlerUK

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
Top