How about 12?

I don't have a life so got lots of time<g


Gord

On Fri, 23 Oct 2009 16:01:46 -0500, Dave Peterson
wrote:

I tried it 3 or 4 times with no problems.

How many times is multiple?

TG wrote:

Dave,

can you try this multiple times.. and let me know what happens?

when I run it the first time is fine but when I run it the second time it
over writes all of the newly created worksheets except the first one...

I am using this macro in a button if that helps...

thanks

"Dave Peterson" wrote:

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
.