| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
spreadsheet overwrite problem! I dont know what is wrong in my cod
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
spreadsheet overwrite problem! I dont know what is wrong in my cod
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
spreadsheet overwrite problem! I dont know what is wrong in my
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
spreadsheet overwrite problem! I dont know what is wrong in my
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
spreadsheet overwrite problem! I dont know what is wrong in my
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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
spreadsheet overwrite problem! I dont know what is wrong in my
Hi again TG
The line should have been: ActiveSheet.Paste Destination:=shB.Range("A1") Regards, Per "TG" skrev i meddelelsen ... 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
spreadsheet overwrite problem! I dont know what is wrong in my
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 "Per Jessen" wrote: Hi again TG The line should have been: ActiveSheet.Paste Destination:=shB.Range("A1") Regards, Per "TG" skrev i meddelelsen ... 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| dont overwrite excel 2003 when installing office 2007 | Excel Discussion (Misc queries) | |||
| Prevent PivotTable Overwrite Warning and Disallow Overwrite | Excel Programming | |||
| recieved attachment. as excel spreadsheet, how do I open?I dont. | Excel Discussion (Misc queries) | |||
| help NEEDED URGENTLY (I HAVE TRIED SOME SUGGESTIONS BUT THEY DONT WORK UNLESS I AM DOING THEM WRONG | Excel Worksheet Functions | |||
Linear Mode
