Copy sheet to next un-named Workbook

[Jimbob]

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 Before:=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 doesn’t refer to the specific filename.

Please help

 

[Dave Peterson]

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 _
before:=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 _
before:=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 _
before:=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.