SHEE1. Duplicate all sheets, making 2 copies of ea. / 2. Add " E"and " F" to end of tab name. / 3.

[StargateFanNotAtHome]

Good Afternoon!

I'm hoping someone can help with a complicated script. I have two
very large workbooks to convert to an English and French version (they
currently have data in both languages, merged). I've thought and
thought about how to handle this about this and the easiest way is to
have Excel create 2 copies of each worksheet, sending those copies to
the end.

This is the the output we would need, from this: SHEET1, SHEET2,
SHEET3 ...

to this: SHEET1, SHEET2, SHEET3 SHEET1 E, SHEET1 F SHEET2 E,
SHEET2 F SHEET3 E, SHEET3 F ... (I don't think any of the tabs are
too long that the extra letter will be truncated out ... ... but
perhaps "error trapping" needed for that; I believe that's what it's
called?)

Can this be done?

Would this starting point help at all (from another script kindly
provided by this ng):

Sub WORKBOOK_Split_into_ENG_and_FRE()

Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
....

Thanks!!! D

 

[StargateFanNotAtHome]

Well, it's just a good thing that I've managed to learn a few tricks
because googling straight questions just doesn't help. I again googled
but on part of the syntax kindly provided for another workbook-
splitting need (something I've learned to do recently), and I came
across something that is close to doing what's needed. Hopefully
someone knows how to modify it because I have wasted so much time
already searching yet I'm not closer to getting a complete solution
<sigh>. But this does help:

Googling for the syntax eventually led me here: http://support.microsoft.com/kb/288402
where I found this partial result:

-------------------------------------
Sub Copier4()
Dim x As Integer

For x = 1 To ActiveWorkbook.Sheets.Count
'Loop through each of the sheets in the workbook
'by using x as the sheet index number.
ActiveWorkbook.Sheets(x).Copy _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
'Puts all copies after the last existing sheet.
Next
End Sub
-------------------------------------

This script gets me this type of result

Before:
SHEET1, SHEET2, SHEET3

After:
SHEET1, SHEET2, SHEET3, SHEET1 (2), SHEET2 (2), SHEET3 (2)

Not quite there yet, to achieve:
SHEET1, SHEET2, SHEET3, SHEET1 E, SHEET1 F, SHEET2 E, SHEET2 F, SHEET3
E, SHEET3 F

Thank you!! D

 

[StargateFan]

On Thu, 3 Sep 2009 12:43:00 -0700 (PDT), StargateFanNotAtHome

[snip]

-------------------------------------
Sub Copier4()
Dim x As Integer

For x = 1 To ActiveWorkbook.Sheets.Count
'Loop through each of the sheets in the workbook
'by using x as the sheet index number.
ActiveWorkbook.Sheets(x).Copy _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
'Puts all copies after the last existing sheet.
Next
End Sub
-------------------------------------

This script gets me this type of result

Before:
SHEET1, SHEET2, SHEET3

After:
SHEET1, SHEET2, SHEET3, SHEET1 (2), SHEET2 (2), SHEET3 (2)

Not quite there yet, to achieve:
SHEET1, SHEET2, SHEET3, SHEET1 E, SHEET1 F, SHEET2 E, SHEET2 F, SHEET3
E, SHEET3 F

Thank you!! D

Can anyone point me in the right direction, at least? I'm not a
natural at this by any means, as many of you know, but I can continue
to try finding what's needed if someone could at least pass along some
code to search for that might help. That's always been the problem,
not knowing the terms to search for so even the help file is difficult
to wade through.

Thanks so much. I guess it's a difficult one, this one, eh? D

 

[StargateFanNotAtHome]

(I'm sorry, I'm guessing lots of people on holiday, etc., so didn't
want to bother the group with yet another request for help. I did
something I've never done before; I've signed up with a forum. The
new person starts Tuesday, so really needed a solution for this
workbook and I'm just not good enough to figure out big changes on my
own. I had great help over there. I certainly did not write this
code myself but hope it's okay to post the solution. I know that when
people google for questions and solutions, our posts help a lot. I've
certainly done more in last 6 months from googling than I've actually
posted, so I can attest to this! <g>).

Here is a solution that I've tested and seems to work just great:

------------------------------------
Function ShExists(ShName As String) As Boolean
Dim ws As Worksheet

On Error Resume Next
Set ws = ThisWorkbook.Worksheets(ShName)
On Error GoTo 0

If Not ws Is Nothing Then ShExists = True
End Function
Sub DuplicateSheetsTWICE_Plus_Rename()
' This provides a means to split a large workbook with bilingual data
into two workbooks, languages separate.
' At this contract, complex mail merge was used on the bilingual one
which created headaches.
' - By splitting the book, half the logistical problems will be
eliminated.

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If Not ShExists(wks.Name & Chr(32) & "E") Then
wks.Copy After:=ThisWorkbook.Worksheets
(ThisWorkbook.Worksheets.Count)
ThisWorkbook.Worksheets
(ThisWorkbook.Worksheets.Count).Name _
= wks.Name & Chr(32) & "E"
End If
If Not ShExists(wks.Name & Chr(32) & "F") Then
wks.Copy After:=ThisWorkbook.Worksheets
(ThisWorkbook.Worksheets.Count)
ThisWorkbook.Worksheets
(ThisWorkbook.Worksheets.Count).Name _
= wks.Name & Chr(32) & "F"
End If
Next
End Sub