Copy Button Problem
I have a userform that allows a user to Save individual worksheets from a
workbook. To make this happen, I had to write a macro to copy the individual worksheet to new workbook, and then have the Save As dialog box appear. When I copy the sheet, the Command Buttons are also copied. Is there a way to eliminate the buttons from being copied? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200604/1 |
Copy Button Problem
one way would be to create a new workbook and then copy and paste the cells
dim sh as worksheet, bk as workbook set sh = activesheet set bk = workbooks.Add sh.cells.copy destination:=bk.Worksheets(1).cells then you won't copy any code associated with the commandbuttons either. -- Regards, Tom Ogilvy "mtm4300 via OfficeKB.com" wrote: I have a userform that allows a user to Save individual worksheets from a workbook. To make this happen, I had to write a macro to copy the individual worksheet to new workbook, and then have the Save As dialog box appear. When I copy the sheet, the Command Buttons are also copied. Is there a way to eliminate the buttons from being copied? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200604/1 |
Copy Button Problem
Thank you Tom. That is awesome.
One other quick question, and I know this probably very simple. Is there anyway to make a certain cell active on the copied worksheet? I cannot put Range(C2).Select in the original workbook because it is the active workbook. And I cannot put that code in the copied worksheet because it has not been created yet. Tom Ogilvy wrote: one way would be to create a new workbook and then copy and paste the cells dim sh as worksheet, bk as workbook set sh = activesheet set bk = workbooks.Add sh.cells.copy destination:=bk.Worksheets(1).cells then you won't copy any code associated with the commandbuttons either. I have a userform that allows a user to Save individual worksheets from a workbook. To make this happen, I had to write a macro to copy the individual worksheet to new workbook, and then have the Save As dialog box appear. When I copy the sheet, the Command Buttons are also copied. Is there a way to eliminate the buttons from being copied? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200604/1 |
Copy Button Problem
In the context of the code I provided
dim sh as worksheet, bk as workbook set sh = activesheet set bk = workbooks.Add sh.cells.copy destination:=bk.Worksheets(1).cells sh.Range("C2").Select Realistically, You can only select on the activesheet. When you copy a sheet or add a workbook or add a sheet, it is the activesheet until you change it. -- Regards, Tom Ogilvy "mtm4300 via OfficeKB.com" wrote: Thank you Tom. That is awesome. One other quick question, and I know this probably very simple. Is there anyway to make a certain cell active on the copied worksheet? I cannot put Range(C2).Select in the original workbook because it is the active workbook. And I cannot put that code in the copied worksheet because it has not been created yet. Tom Ogilvy wrote: one way would be to create a new workbook and then copy and paste the cells dim sh as worksheet, bk as workbook set sh = activesheet set bk = workbooks.Add sh.cells.copy destination:=bk.Worksheets(1).cells then you won't copy any code associated with the commandbuttons either. I have a userform that allows a user to Save individual worksheets from a workbook. To make this happen, I had to write a macro to copy the individual worksheet to new workbook, and then have the Save As dialog box appear. When I copy the sheet, the Command Buttons are also copied. Is there a way to eliminate the buttons from being copied? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200604/1 |
All times are GMT +1. The time now is 10:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com