Duplicate a sheet over multiple tabs.

[X82]

Hi again!

I have a large excel sheet which contains all my data and tables etc. I need to copy this in the same worksheet 30 times. But right clicking and copying each time takes a while since its a big sheet. Is there a macro that will copy my current sheet in the same workbook a specific amount of times?

 

[Smitty]

Welcome to the Board!

Here's something I use all the time:

<font face=Calibri><SPAN style="color:#00007F">Public</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Public</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> CopySheetXTimes()<br>    <SPAN style="color:#00007F">Dim</SPAN> NewName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        i = Application.InputBox("How many copies do you want?", "Number of Copies?", Type:=1)<br>        <br>        <SPAN style="color:#00007F">Set</SPAN> ws = ActiveSheet<br>        <br>        <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> i<br>            ws.Copy After:=Sheets(Sheets.Count)<br>                    NewName = InputBox("Enter the new sheet name", "New Sheet Name")<br>            ActiveSheet.Name = NewName<br>        <SPAN style="color:#00007F">Next</SPAN> x<br>        <br>        ws.Activate<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[X82]

Thank you!
Amazing. I love this forum!

 

[Alan C]

I pasted this code and I can't seem to get it to work. Any advise would be appreciated.

Thanks,

Alan c

 

[johnmeyer]

I pasted this code and I can't seem to get it to work. Any advise would be appreciated.

Thanks,

Alan c

Oops, made a mistake in my post. I should have said, MOVE the Sub statement in the third line so that it is the first line.

Actually, I just copy/pasted the code and it worked fine here, so you must be making a mistake in your copy/paste.

 

[Smitty]

Oops, made a mistake in my post. I should have said, MOVE the Sub statement in the third line so that it is the first line.

Actually, those variables are declared Publicly, so they need to be outside of the Sub procedure.

I pasted this code and I can't seem to get it to work. Any advise would be appreciated.

What happens if you step through the code (F8)? Do you have it in a general module? Are you getting any error messages?