Copy all worksheets to another workbook, excl. duplicate sheets already in other workbook

[ARbitOUR]

Hi all!

I must say that this forum has been helpful in leaps and bounds!

Anyways, I have code that cycles through all sheets in workbook1, and
then copies a specific range to the corresponding worksheets (with like
names) in workbook2. [PHASE 1]

Now my problem is that I need code that will cycle through all
worksheets in workbook1, and compare the names of them to those in
workbook2. If a worksheet (i.e. worksheet name) exists in the first
workbook, but not in the second workbook, then this specific sheet
(including all formulas, formats etc.) in its entirety must be copied to
the second workbook...[PHASE 2]

Bear in mind that an indefinite number of worksheets will be added over
time (into workbook1), of which the sheet names will be unknown (i.e. I
may not use specific sheet names in the macro (i.e worksheets
("ListA").Copy, etc. can not be used. ALSO, The amount of sheets
workbook1 is unknown.

Makes sense?
If it does, please help!!!
SOS


Here's the code I've got for the first phase:

Sub Copy_Stuff ()

Dim TargetWB As Workbook
Dim SourceWB As Workbook
Dim TargetWS As Worksheet
Dim SourceWS As Worksheet
Dim SourceRange As Range

Set TargetWB = Workbooks.Open(ThisWorkbook.Path & "\Price Lists1.xlsm",
ignorereadonlyrecommended:=True)

Set SourceWB = Workbooks.Open(ThisWorkbook.Path & "\Price Lists2.xlsm",
ignorereadonlyrecommended:=True)


Workbooks("Price Lists2.xlsm").Activate
For Each SourceWS In Worksheets
Set SourceRange = SourceWS.Range("C13:X100")

Workbooks("Price Lists1.xlsm").Activate
For Each TargetWS In Worksheets
If TargetWS.Name = SourceWS.Name Then
SourceRange.Copy
TargetWS.Range("C13").PasteSpecial xlPasteAll

'PLACE NEW CODE HERE...??? after ELSE
statement...???

Exit For
End If
Next TargetWS
Next SourceWS

Set TargetWB = Nothing
Set SourceWB = Nothing
Set SourceRange = Nothing

End Sub

Any Idea of code I can use for phase 2 in the ['PLACE NEW CODE
HERE...??? after ELSE statement...???] area?

Any other suggestions are also welcome.

Thanx in advance...
ARbitOUR

 

[Chip Pearson]

Use code like the following:

Dim WB1 As Workbook
Dim WB2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim B As Boolean

Set WB1 = Workbooks("Book1.xls") '<< CHANGE
Set WB2 = Workbooks("Book2.xls") '<< CHANGE
On Error Resume Next
For Each WS1 In WB1.Worksheets
B = Not IsError(WB2.Worksheets(WS1.Name))
If B = True Then
With WB2.Worksheets
WS1.Copy after:=.Item(.Count)
End With
End If
Next WS1

This will copy all sheets in WB1 to the end of the worksheets in WB2,
creating a new worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ARbitOUR]

Hi there. Thx for the code.

Unfortunately (with a bit of hindsight) I realized that adjusting my
code to allow the changes I requested would actually cause other
problems that would negate certain functions of other code I already
have in the applicable workbooks.


Thanx though for the interest!

ARbitOUR