Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet with named ranges to new workbook and keep names in | Excel Programming | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
Copy A Named Range To a Different Sheet | Excel Programming | |||
Please Help! Copy named range to new workbook without changing references. | Excel Programming | |||
Copy a Named Range to a New Workbook without changing the Reference | Excel Programming |