Copy sheet to next un-named Workbook
Hi knowledgeable people
I have recorded some code to copy a worksheet from the current workbook (which also contains the code) to the previously open workbook. From my recording, I get this code: Sheets("Record Check").Copy Befo=Workbooks("Template2009.xls").Sheets(20) My problem is that the name of the destination workbook is variable and I use ActiveWindow.ActivateNext to step between them. So I need to use code which doesnt refer to the specific filename. Please help |
Copy sheet to next un-named Workbook
That would scare me.
If the sheet to get copied is in the workbook with the code, I think I'd ask the user to activate the receiving workbook before running the macro--maybe even ask them to activate the worksheet where the worksheet gets inserted. thisworkbook.worksheets("record check").copy _ befo=activesheet ====== If that's not possible, I still wouldn't let the code guess at what the receiving workbook should be. I'd either ask (with a userform displaying the open workbook names or even just letting them click on a cell on a worksheet in the receiving workbook. Dim destCell as range set destcell = nothing on error resume next set destcell = application.inputbox _ (Prompt:="select a cell in the receiving workbook",type:=8) _ .cells(1) on error goto 0 if destcell is nothing then 'user hit cancel, what should happen" else thisworkbook.worksheets("record check").copy _ befo=destcell.parent 'worksheet with selected cell 'or if you want it before sheet 20 '(and there better be at least 20 sheets! thisworkbook.worksheets("record check").copy _ befo=destcell.parent.parent.sheets(20) end if You could even rearrange the windows (tiled) before showing the application.inputbox. Or let the user go through the Window option (on the xl2003 menubar) or the View tab|window group of the xl2007 ribbon. Jimbob wrote: Hi knowledgeable people I have recorded some code to copy a worksheet from the current workbook (which also contains the code) to the previously open workbook. From my recording, I get this code: Sheets("Record Check").Copy Befo=Workbooks("Template2009.xls").Sheets(20) My problem is that the name of the destination workbook is variable and I use ActiveWindow.ActivateNext to step between them. So I need to use code which doesnt refer to the specific filename. Please help -- Dave Peterson |
All times are GMT +1. The time now is 09:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com