Your code worked fine for me.

But I would have used:
shB.Paste Destination:=shB.Range("A1")
instead of:
ActiveSheet.Paste Destination:=shB.Range("A1")

My test code was in a general module of activeworkbook (specifications.xls in
your sample).

TG wrote:

I have a workbook called book1.xls and a workbook called specifications.xls

I have a macro:

Dim wbA As Workbook
Dim wbB As Workbook
Dim shA As Worksheet
Dim shB As Worksheet

Set wbA = ThisWorkbook
Set wbB = Workbooks.Open(ThisWorkbook.Path & "\book1.xls")
Set shA = wbA.Sheets("BOM")
shA.Range("A1:O66").Copy

Set shB = wbB.Sheets.Add
ActiveSheet.Paste Destination:=shB.Range("A1")
'shB.Name = shB.Range("C62")

With shB
.Columns("O:O").EntireColumn.AutoFit
.Columns("C:C").ColumnWidth = 10.71
.Columns("C:C").ColumnWidth = 13.57
.Columns("B:B").ColumnWidth = 12
.Range("A1").Select
End With
wbB.Close SaveChanges:=True
End Sub

this copies what ever is in "BOM" sheet on specifications.xls and then opens
book1.xls and paste the data on a new worksheet..

This works fine the first time but then the second time it creates a new
worksheet on book1.xls (which is a good thing) but it over writes the
previous worsheet!

why is this happening? I need it to copy the data to a new worksheet and not
overwrite any data...
Any help?

Thank you,

TG

--

Dave Peterson