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.
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 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
|
Similar Threads
