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