VBA Excel Help: Copying and Renaming Worksheets in Bulk

[UnleashtheData]

Hi! Very novice VBA coder here hoping some of you experts can help me out! If I can get this macro, it will be used a lot and save a ton of time!

Basically, I am hoping for a macro that can copy and rename worksheets in bulk, placing the new worksheets starting just after the selected worksheet. Either through an input box or a reference to a range, I hope to be able to enter the desired names for the new worksheets, and to specify which sheet the new sheets should be copied from / modeled after. The number of new worksheet names I enter will vary; I would want the macro to copy and rename the same number of worksheets as the number of names I enter.

For example, say I currently have the following worksheets: "SheetC", "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5". Say I have "Sheet2" selected when I run the macro, and that I indicate that I want the copies to come from "SheetC". Say I enter the following as the names for the new Worksheets: "SheetA", "SheetB", "SheetC", "SheetD".

Ideally, I would want the result to be as follows: "SheetC", "Sheet1", "Sheet2", "SheetA", "SheetB", "SheetC.1", "SheetD", "Sheet3", "Sheet4", "Sheet5", and each of the new sheets would be copies of the original Sheet C.

(In this example, I asked it to rename a sheet to a name that was already taken. In cases like this, which will happen often, I would love the macro to add a ".1" to the end of the new worksheets name. There will be situations in which this name is also taken, in which case the name would end in ".2". Or ".3" if needed, etc.)

Thank you all so much!!!

 

[JLGWhiz]

If you want to copy and name all the sheets in the workbook with 5 sheets, and you have the new names listed in cells A1:A5 of Sheets(1) then

Sub t()
Dim i As Long
For i = Sheets.Count To 1 Step -1
    Sheets(i).Copy After:=Sheets(i)
    ActiveSheet.Name = Sheets(1).Range("A" & i).Value
Next
End Sub

Excel can create a sheet with the same name with a suffix like Sheet1(2), But it will not let you use an identical sheet name in the same workbook. It gives you an error message and forces you to manually alter the name to something different.

 

[UnleashtheData]

Thank you; that looks like a great step towards what I want!

I noticed that this copies every sheet once. Instead of copying each sheet a certain number of times, I am hoping for one that copies a single sheet a number of times (the number of times being dependent on the number of names I input).

Would that be possible, perhaps using a loop?

Thank you!

If you want to copy and name all the sheets in the workbook with 5 sheets, and you have the new names listed in cells A1:A5 of Sheets(1) then

Sub t()
Dim i As Long
For i = Sheets.Count To 1 Step -1
    Sheets(i).Copy After:=Sheets(i)
    ActiveSheet.Name = Sheets(1).Range("A" & i).Value
Next
End Sub

Excel can create a sheet with the same name with a suffix like Sheet1(2), But it will not let you use an identical sheet name in the same workbook. It gives you an error message and forces you to manually alter the name to something different.

 

[UnleashtheData]

Any additional help? Thank you!!