Copy and rename tabs

mike_ate_a_pie · Apr 25, 2010

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5Cmichaelm%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5Cmichaelm%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5Cmichaelm%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} p.MsoNoSpacing, li.MsoNoSpacing, div.MsoNoSpacing {mso-style-priority:1; mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Hey troops,
<o

> </o

>
I don’t know if this is possible but I frequently have to copy numerous tabs, go though each of them and then rename them slightly.With loads of tasks it makes this quite annoying.

<o

> </o

>
For example if I have the following tabs- 1,2,3,...
<o

> </o

>
I will then have to go through each tab and rename them say A - 1, A – 2, A – 3, ... and then rename the copied tabs say B – 1, B – 2, B -2, ...
<o

> </o

>
Is there any way I could select what to insert in front of the selected tabs to copy – (eg B) and then automatically run a macro to copy the selected tabs into the workbook.Perhaps also select what to insert for the first set of tabs aswell (eg A).

This would be fantastic if so...

<o

> </o

>
Thanks

Domenic · Apr 25, 2010

The following macro will make a copy of each worksheet in the workbook, add the prefix B- to the sheet tab of each copy of the worksheet, and add the prefix A- to the sheet tab of each original worksheet.

Code:

Option Explicit

Sub test()

    Dim Wksht As Worksheet

    For Each Wksht In Worksheets
        Wksht.Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = "B-" & Wksht.Name
        Wksht.Name = "A-" & Wksht.Name
    Next Wksht
    
End Sub

mike_ate_a_pie · Apr 25, 2010

Thanks this is great, is there a way to denote in the spreadsheet what gets inserted after each tab

mike_ate_a_pie · Apr 25, 2010

This is what I'm trying to do. I have this code which copies my selected sheets and inserts a new company. When doing so after the new tab comes "(2)" as expected. If I could have a cell in a another tab that the user could choose, eg enter "Mr Excel Limited", this would appear instead of the 2.

Sub NewCompany()
'
' NewCompany Macro
'

'
Sheets(Array("Assumptions", "Yearly", "Monthly", "Sales Calc", "Sales and COS", _
"FAs 1", "Loan", "HP 1")).Select
Sheets("Assumptions").Activate
Sheets(Array("Assumptions", "Yearly", "Monthly", "Sales Calc", "Sales and COS", _
"FAs 1", "Loan", "HP 1")).Copy After:=Sheets(4)
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("TOC").Select
ActiveWindow.SelectedSheets.Delete
Application.Run "'Forecasts with IFRS.xlsm'!CreateTOC"
End Sub

This would be amazing,

thanks

Domenic · Apr 25, 2010

Assuming that A2 of Sheet4 contains 'Mr Excel Limited', maybe something like this...

Code:

Option Explicit

Sub NewCompany()

    Dim MyArray As Variant
    Dim i As Long
    Dim strName As String
    
    MyArray = Array("Assumptions", "Yearly", "Monthly", "Sales Calc", _
                "Sales and COS", "FAs 1", "Loan", "HP 1")
    
    strName = Sheets("Sheet4").Range("A2")
    
    For i = 0 To UBound(MyArray)
        Sheets(MyArray(i)).Copy After:=Sheets(4 + i)
        ActiveSheet.Name = MyArray(i) & " " & strName
    Next i
    
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    
    Application.DisplayAlerts = False
    Sheets("TOC").Delete
    Application.DisplayAlerts = True
    
    Application.Run "'Forecasts with IFRS.xlsm'!CreateTOC"

End Sub

mike_ate_a_pie · Apr 26, 2010

this is ace, i almost shed a tear!

Going on from this would it be possible to run a macro to rename the existing tabs from above. For example you rename the copied tabs Mr excel linkng to Sheet 4, B2 and rename the existing one from cell Sheet 4, A2.

I may need the tissues if so...

thanks

Domenic · Apr 26, 2010

mike_ate_a_pie said:
this is ace, i almost shed a tear!

Going on from this would it be possible to run a macro to rename the existing tabs from above. For example you rename the copied tabs Mr excel linkng to Sheet 4, B2 and rename the existing one from cell Sheet 4, A2.

Try...

Code:

Option Explicit

Sub NewCompany()

    Dim MyArray As Variant
    Dim i As Long
    Dim strName1 As String
    Dim strName2 As String
    
    MyArray = Array("Assumptions", "Yearly", "Monthly", "Sales Calc", _
                "Sales and COS", "FAs 1", "Loan", "HP 1")
    
    strName1 = Sheets("Sheet4").Range("A2")
    strName2 = Sheets("Sheet4").Range("B2")
    
    For i = 0 To UBound(MyArray)
        Sheets(MyArray(i)).Copy After:=Sheets(4 + i)
        ActiveSheet.Name = MyArray(i) & " " & strName2
        Worksheets(MyArray(i)).Name = MyArray(i) & " " & strName1
    Next i
    
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    
    Application.DisplayAlerts = False
    Sheets("TOC").Delete
    Application.DisplayAlerts = True
    
    Application.Run "'Forecasts with IFRS.xlsm'!CreateTOC"

End Sub

I may need the tissues if so...

thanks

You're welcome!

mike_ate_a_pie · Apr 26, 2010

pass the tiissues, thanks

mike_ate_a_pie · Apr 27, 2010

hi again, lol

a further development would be that the macro could be run continously being able to add a second, third, forth set of tabs.

at the momnet it only allows me to run the code once and then fails.

Any ideas?

cheers

Domenic · Apr 27, 2010

Can you describe how the procedure needs to take place?

Copy and rename tabs

mike_ate_a_pie

Board Regular

Excel Facts

Domenic

MrExcel MVP

mike_ate_a_pie

Board Regular

mike_ate_a_pie

Board Regular

Domenic

MrExcel MVP

mike_ate_a_pie

Board Regular

Domenic

MrExcel MVP

mike_ate_a_pie

Board Regular

mike_ate_a_pie

Board Regular

Domenic

MrExcel MVP

Similar threads

Forum statistics

Share this page

Copy and rename tabs

Board Regular

Excel Facts

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock