Multiple Copies of one sheet and quick rename

M

MikeyD2

New Member
Joined
May 1, 2010
Messages
2
I have a sheet that i need to make 70 copies of (in the same workbook) and I would like to rename them 1-70. I know how to select tab>Move or Copy>create a copy and so on, but this takes to long to make that many copies and rename 1 by 1. Is there a way to make all the copies i need and rename them quickly. I am using MS Office 2007. any help with this would be greatly appreciated.

thanks
MikeyD2
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Download and instal ASAP Utilities, it's an free add in for Excel that starts when Excel starts and gives you loads of extra functions at the click of your mouse. Under the 'Sheets' menu, item 2 is 'Insert multiple sheets (uses the cell values as sheet names)'. Put the numbers 1 to 70 in a column and select them all, select the above function and it asks you if you want to create blank sheets or use a current sheet as a template. Select the sheet you want as a template from the list of your sheets and click OK.

http://www.asap-utilities.com

HTH
Regards
Paul
 
Upvote 0
Try...

Code: 
Option Explicit

Sub CopyWksht()

    Dim Wksht As Worksheet
    Dim i As Long
    
[COLOR="SeaGreen"]'   Change the worksheet name accordingly[/COLOR]
    Set Wksht = Worksheets("Sheet1")
    
    For i = 1 To 70
        Wksht.Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = i
    Next i
    
End Sub
 
Upvote 0
pboltonchina said:
Download and instal ASAP Utilities, it's an free add in for Excel that starts when Excel starts and gives you loads of extra functions at the click of your mouse. Under the 'Sheets' menu, item 2 is 'Insert multiple sheets (uses the cell values as sheet names)'. Put the numbers 1 to 70 in a column and select them all, select the above function and it asks you if you want to create blank sheets or use a current sheet as a template. Select the sheet you want as a template from the list of your sheets and click OK.

http://www.asap-utilities.com

HTH
Regards
Paul
Click to expand...

thanks paul i really like this plugin so i might buy it

Domenic said:
Try...

Code: 
Option Explicit

Sub CopyWksht()

    Dim Wksht As Worksheet
    Dim i As Long
    
[COLOR=SeaGreen]'   Change the worksheet name accordingly[/COLOR]
    Set Wksht = Worksheets("Sheet1")
    
    For i = 1 To 70
        Wksht.Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = i
    Next i
    
End Sub
Click to expand...

thanks for the suggestion
 
Upvote 0
pboltonchina said:
Download and instal ASAP Utilities, it's an free add in for Excel that starts when Excel starts and gives you loads of extra functions at the click of your mouse. Under the 'Sheets' menu, item 2 is 'Insert multiple sheets (uses the cell values as sheet names)'. Put the numbers 1 to 70 in a column and select them all, select the above function and it asks you if you want to create blank sheets or use a current sheet as a template. Select the sheet you want as a template from the list of your sheets and click OK.

ASAP Utilities for Excel - The popular add-in for Excel users. Easy to use tools that save time and speed up your work in Excel. We also offer a free edition.

HTH
Regards
Paul
Click to expand...


Thank you Mr. Paul you have saved my day from copying and copying non stop what a wonderful APP it WORKS!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA help required in creating multiple workbooks from one workbook.
Replies
5
Views
280
jolivanes
jolivanes
T
  • Question
Help updating tab reference automatically or with macro
Replies
2
Views
237
MARK858
MARK858
S
  • Solved
Macro to Rename worksheet tabs
Replies
6
Views
281
Joe4
Joe4
J
  • Question
No formulas working to rename sheets within workbook
Replies
5
Views
132
Joe4
Joe4
tourless
  • Solved
Programmatically renaming sheets with variables
Replies
5
Views
684
Joe4
Joe4

Forum statistics

Threads
1,214,881
Messages
6,122,074
Members
449,064
Latest member
MattDRT

Share this page

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