Duplicating multiple sheets multiple times manually is boring and time consuming. In this tutorial, you will find a handful of macros to automate this task. Continue reading
Comments on: How to duplicate sheet in Excel with VBA
by Svetlana Cheusheva, updated on
Table of contents
Comments page 2. Total comments: 44
Dear Sir,
I have a workbook in which a sheet with some formulas. I want a duplicate copy of this sheet and rename with a cell value. And I want this duplicate sheet with Cell values only (as paste special). I did it with the help of your formulas but it is copy and pasting on the source sheet not on duplicate sheet. Please help me.
Private Sub CommandButton1_Click()
Dim wks As Worksheet
Set wks = ActiveSheet
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wks.Range("b9").Value "" Then
On Error Resume Next
ActiveSheet.Name = wks.Range("b9").Value
End If
CommandButton2_Click
'wks.Activate
End Sub
Private Sub CommandButton2_Click()
Worksheets(Sheets.Count).Activate
Range("a1:s36").Copy
Range("a1:s36").PasteSpecial xlPasteValues
End Sub
Morning all,
The above macros are extremely useful, but is there a way to copy the sheet across as "values only", similar to the PasteSpecial method?
Could anyone please help me on how to copy sheets with shapes?
My code is referencing to a specific shape, therefore when I copy the sheet, names of the shapes change.
How can I prevent this?
Is there a way to write a code that doesn't have to reference each shape?
Thank you for your help.
Hello,
I am trying to duplicate a excel sheet multiple times, rename them based on an Excel List, and then populate the column next to the sheet name with a link to the sheet.
The list is found in the "Cover" sheet and I want to copy the "Template" sheet based on the list selection. The link shortcut will be in the cover sheet in the column next to the cell that was used to generate the sheet name. There will be a lot of tabs and this will make life a lot easier if there is a link from the cover page
Is this possible, and if so how can I do it?
Hi
Is it possible to overwrite a sheet of the same name in the worksheet where the sheet is to be copied? I need to be able to copy the same sheet from time to time. Please help?
Hi,
Your macros have been extremely helpful.
I really appreciate your work. Thank you.
Thank you so much.
Excel macro to copy sheet and rename
Is there anyway for the button to be on one sheet but instead of copying the active sheet it copies the sheet called New Hire?
Excel VBA to duplicate sheet multiple times:
This is the closest to what I am trying to do. But all the new sheets are named he same as the original but with (x). i.e. Original sheet is 10001 and I need them to number 10002, 10003, etc. But they are numbering 10001, 10001(1), 10001(2).
How can I get my sheet tabs to number the way I want? Any help would be greatly appreciated.
Thanks!
Thanks for this vba "Excel VBA to copy sheet from another workbook without opening" which works excellently in its current form. Can you please tweak it a little so that instead of giving a specific path, a window opens and I select the file from which data needs to be pulled. Rest it should remain the same. Thanks in advance.
Hi I have used your code titled "Excel VBA to duplicate sheet multiple times"
but how shall I rename the sheets? I want them to be in numbers.
Eg
Original Sheet is "master'
Copy need 5
result shall be in sheet name as
Point 1
Point 2
Point 3
Point 4
Point 5
Please help
---[Snip, Snip]---
Public Sub DuplicateSheetMultipleTimes()
Dim n As Integer
On Error Resume Next
n = InputBox("How many copies of the active sheet do you want to make?")
If n >= 1 Then
For numtimes = 1 To n
activeSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
ActiveSheet.name = "Point " + CStr(numtimes)
Next
End If
End Sub
---[Snip, Sni
This will cause the various copied sheets to be renamed as they are copied
Hi,
I have a workbook with many sheets. I need particular cells copied from sheet 1,sheet2 of Workbook1 and show it in another workbook. For example I have the dealer ID and name in Sheet1 and the rating in sheet5 of the same workbook1. I need to pull the rating using ID which is unique to another worksheet and then I need the ID field to be dynamic. How do I do it? please advise.
thanks so much.
Is it possible to pick up a range of tabs? For example, the macro requires that I list the tab names individually ie.Sheets(Array ("Tab1", "Tab2", "Tab3", “etc…”)).Copy)
What if I want to pick up all the tabs between "Tab1" and "Tab8" without listing them all individually? Is this possible?
Hello, your macros have been extremely helpful.
I was wondering if the following can be achieved
1) I select a range, say A2-A15, which contains the names of worksheets in a particular workbook.
2) I want to create a new workbook with the selected worksheet names from the active workbook using the following formula:
“
Sub Copy_Worksheets()
Worksheets(Array("ACC_HF", "ACC_Aflac", “etc…”)).Copy
End Sub
”
Is there a way to convert the selected range and pass is to the array argument?
How can i copy data from a range of cells say B5:I39 from multiple worksheets and paste them to one new worksheet, i want to be able to have all selected worksheets with specific data range to be copied to one worksheet at the end of the workbook. All formatting from selected sheets to be coppied across too.
Thanks for the tip, is there a way to copy a row in one workbook and paste that row of data to a different workbook on a specific cell? There is a catch though, the data will need to sync to a different tab of the same workbook every other month.
That's great
how can I copy sheets containing a specific text string somewhere on the sheet, to a new workbook?