Dim sh as Worksheet, sh1 as Worksheet, Sh2 as Worksheet
Dim sh3 as Worksheet
Dim i as Long
i = 100
' rename destination sheets with dummy names to
' avoid duplicate sheet name problems
for each sh3 in workbooks("DestBook.xls").Worksheets
sh3.Name = "zxaabir" & i
i = i + 1
Next
sh1 = Workbooks("DestBook.xls").Worksheets(1)
for each sh in workbooks("SourceBook.xls")
sh.cells.copy
sh1.Cells.PasteSpecial xlFormats
sh1.Cells.PasteSpecial xlFormulas
sh1.Name = sh.Name
set sh2 = Nothing
On Error Resume Next
set sh2 = sh1.Next
On Error goto 0
if not sh2 is nothing then
set sh1 = sh2
else
set sh1 = worksheets.Add(After:=sh1.Parent.Worksheets( _
sh1.Parent.Worksheets.count))
end if
Next

--
Regards,
Tom Ogilvy


"Kevin H. Stecyk" wrote in message
...
Hi,

I am trying to copy formulas and formats from one workbook (source) to
another workbook (target). I discovered that the source book doesn't have
its sheets in numerical order and some are missing. In other words, you
might have Sheet1("FirstSheet"), Sheet4("SecondSheet"),
Sheet3("ThirdSheet"), [Sheet2 doesn't exist.] and so on.

I believe I need to work with the names instead of sheet numbers.

To get myself started, I recorded the following macro:

Sub Macro1()
'
Windows("SourceBook.xls").Activate
Sheets("SourceSheet").Select
Cells.Select
Range("A3").Activate
Selection.Copy
Windows("TargetBook.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
End Sub

From that, I created the following macro:

Public Sub CopyContentsFormat(lMin As Long, lMax As Long)
Dim index As Long
For index = lMin To lMax
'\This line below doesn't work - see explanation
Windows("SourceBook.xls").Worksheets(index).Cells. Copy
Windows("SourceBook.xls").Worksheets("Sheet" & index).Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
'\How do I copy/transfer the sheet names from the source book to
the
'\ target book?
Next index
End Sub

I get the following error for the line that does not work:

Run-Time Error '438'
Object doesn't support this property or method.

When it didn't work, the index was 2, which was lmin. Sheet2 does exist.
So I am not sure what went wrong. But as mentioned above, I think I need
to
work with the sheet names anyway, because of missing Sheet numbers and the
sheet order. I think I need to read the sheetnames into an array, and
then
work with the sheet names?

Also, I would like to transfer the sheet names from the source book to the
target book. Any help is most appreciated.

Best regards,
Kevin