Data keeps getting overwritten by macro

[TG]

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]

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]

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]

I tried it 3 or 4 times with no problems.

How many times is multiple?

 

[Gord Dibben]

Keeps adding more worksheets to Book1.xls with the data from WbA BOM! A1:O66

Adds a new sheet each time it runs, does not overwrite anything.

You state...........

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...

If you run it once there will be only one newly created worksheet so where
does "all" come from?

I would add one line to the macro

Application.CutCopyMode = False just above End With


Gord Dibben MS Excel MVP

 

[Gord Dibben]

How about 12?

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


Gord