VBA Help

[Silo]

Hello Everyone

I would like to know if there is A VBA solution for the a task I like to complete.

I have a Sheet that I have to copy 61 times. I have another sheet with a list of 61 names. These names represent the names for each sheet. This is the cell range for the 61 names A3:A63

Question is, Can a macro be written that will allow me to easily copy the sheet 61 time and also label that sheets with the 61 names?

I appreciate any help you guys can provide

Thanks

ray:

 

[Tazguy37]

How about something like this?:

Sub test()
Dim i As Long
    Application.ScreenUpdating = False
    
    For i = 3 To 63
        Worksheets("Sheet1").Copy after:=Sheets(Worksheets.Count)
        ActiveSheet.Name = Worksheets("Name sheet").Range("A" & i).Value
    Next i

    Application.ScreenUpdating = True
End Sub

Where Name sheet has the list of names for your worksheets, and Sheet1 is the sheet you wish to copy. Those wouldn't have to be hardcoded, that's just how I did it. Hope that helps!

 

[HalfAce]

Hello Silo,
How about something like this:


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AddSheets()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, shct <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, cell <SPAN style="color:#00007F">As</SPAN> Range
j = ActiveSheet.Name
x = 3
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A3:A63")
    shct = Sheets.Count
    i = Cells(x, 1).Value
    Cells.Copy
    Sheets.Add After:=Sheets(shct)
    ActiveSheet.Paste
    ActiveSheet.Name = i
    Sheets(j).Select
    x = x + 1
<SPAN style="color:#00007F">Next</SPAN> cell
<SPAN style="color:#00007F">With</SPAN> Application
    .CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
    .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Is this pretty close to what you're looking for?
Dan

 

[Silo]

Thanks Guys, I will try both Thanks again

 

[HalfAce]

Pretty slick Taz. I never realized you could add sheets that way.


Dan

 

[Silo]

HalfAce That worked Great

Thanks dude

 

[HalfAce]

You're more than welcome.
(But...) as long as you replace ("Name sheet") with your real sheet name (of the sheet you're copying to all the rest), Taz's code will be a bit quicker & more efficient. :wink:

Dan

 

[Silo]

I tried both, They both work. Thanks Guys