Hi TG

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

Set shA=wbA.Sheets("BOM")

Regards,
Per

"TG" skrev i meddelelsen
...
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" wrote:

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" skrev i meddelelsen
...
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