spreadsheet overwrite problem! I dont know what is wrong in my cod

[TG]

ok so I have the following code:

Sub Button15_Click()
Sheets("BOM").Select
Range("A1:O66").Select
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.Copy
Workbooks.Open (ThisWorkbook.Path & "\book1.xls")
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = Range("C62")
' Range("Q2").Select
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
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

As you can see its a clickable button, once clicked it copies whatever is in
the "BOM" sheet in the active workbook. It then copies it to the clipboard,
then i ask it to open the workbook "book1.xls" and create a new tab, paste
the data and rename the new tab with the value in cell "C62" (lets say this
tab is called "X") and then saves the workbook and closes the workbook.

This works great for the first time, once I make changes to the "BOM" i want
to click the button again and do the same process as above, copying, opening
the book1.xls,create new tab with the value in C62(lets say this tab is
called "Y"), pasting, saving and closing the workbook. It does its job well,
except it overwrites whatever there was in tab "X" with whatever there is now
in tab "Y" ! (the names in the tabs ramain the same as before, if that
matters??)

what is wrong?? please help, this is driving me insane!

Thanks in advance,

TG

 

[Per Jessen]

Hi

Try if this solve the problem:

Sub Button15_Click()
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 = Sheets("BOM")
shA.Range("A1:O66").Copy

Set shB = wbB.Sheets.Add
shB.Range("A1").Paste
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

Regards,
Per

 

[TG]

Hello Per Jessen,

Thank you for you help!

I tried your code and am getting a "subscript out or range" for the line

"Set shA=Sheets("BOM")"

any Ideas as to why??

Thanks again,

TG

 

[Per Jessen]

Hi TG

I forgot to use the workbook reference, this should do it:

Set shA=wbA.Sheets("BOM")

Regards,
Per

 

[TG]

Thanks again!

well that fixed that problem but now am getting a new one..
I now get a " object doesn't support this property or method" for the line

"shB.Range("A1").Paste"

sorry for my ignorance,

Thanks and hope you can help once again,

TG

 

[Per Jessen]

Hi again TG

The line should have been:

ActiveSheet.Paste Destination:=shB.Range("A1")

Regards,
Per

 

[TG]

Hi again Per Jessen

that line worked, however am still getting the problem I originially posted.
The newly created tab's data propagates to all of the previous tabs and now
they all have the same data.

Thanks for you help,

TG