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

mike_ate_a_pie · Apr 27, 2010

this is my code i have now ajusted for, however is it possible to make the new sheets always insert from the 4th tab along, currently it inserts them in the 4th tab from the last set of tabs imported.

thanks

Sub NewCompany()

Dim MyArray As Variant
Dim i As Long
Dim strName1 As String
Dim strName2 As String
Dim suffix As String, ctr As Integer

MyArray = Array("Assumptions", "Yearly", "Monthly", "Sales Calc", _
"Sales and COS", "FAs 1", "Loan", "HP 1")

For Each sh In ThisWorkbook.Sheets
If sh.Name Like "*" & MyArray(0) & "*" Then
suffix = Replace(sh.Name, MyArray(i), "")
ctr = ctr + 1
End If
Next sh

strName1 = Sheets("Overview").Range("D15")
strName2 = Sheets("Overview").Range("D16")

For i = 0 To UBound(MyArray)
Sheets(MyArray(i) & suffix).Copy After:=Sheets(4 * ctr + i)
ActiveSheet.Name = MyArray(i) & " " & strName2
If ctr = 1 Then
Worksheets(MyArray(i)).Name = MyArray(i) & " " & strName1
End If
Next i

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True

Application.Run "'" & ActiveWorkbook.Name & "'!CreateTOC"

End Sub</pre>

Domenic · Apr 27, 2010

Try replacing...

Code:

Sheets(MyArray(i) & suffix).Copy After:=Sheets(4 * ctr + i)

with

Code:

Sheets(MyArray(i) & suffix).Copy After:=Sheets(4 + i)

Copy and rename tabs

mike_ate_a_pie

Board Regular

mike_ate_a_pie

Board Regular

Excel Facts

Domenic

MrExcel MVP

Similar threads

Forum statistics

Share this page

Copy and rename tabs

mike_ate_a_pie

Board Regular

mike_ate_a_pie

Board Regular

Excel Facts

Domenic

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