Moving Excel sheets within VBA works with F8 but fails with F5. Why?

Does it help if you use this:

Sub UploadTemplate()
    Dim wbkSource as Workbook, wbkDest as Workbook
    set wbkdest = ActiveWorkbook
    set wbkSource = Workbooks.Open(Filename:="C:\Templates\DataSourceTemplate.xls")
    wbkSource.Activesheet.Move Before:=wbkDest.Sheets(1)
End Sub

                                              

1:
2:
3:
4:
5:
6:

jppinto: The workbook I'm uploading to is one someone else creates. Placing a button on this sheet is extra confusion for users. My VB macro runs from a separate utilities workbook (or add-in), so I dont' this is practical. I just want to tell them to press Control-shift-U and it will upload this template sheet.

rorya: It sounded promising, and I agree it's better practice to declare stuff.  But sadly it still worked the same way; stepping through with F8 worked, pressing F5 didn't.  I assume yours has worked successfully?  I should point out that my client's site is still running Excel 2003 with VBA 6.3. Maybe that's the problem.

PS: I also tried Copy instead of Move, but that just had the same effect.

Works fine for me in XL2003. Where is the code located, and does the template file have any event code in it?

The code is located in a workbook called Macro.xls which is in a window hidden from Excel users.
The template file has no code of any kind. It's basically a simple form for users to fill in.
I've now repeated it in a clean environment; new workbook with just your version of the code in it, with a new virgin test worksheet as target. It still works the same way (F8 works, F5 or shortcut doesn't). The only other thing is an add-in called 3424.xls (WZTMPL.XLA) which is a given of the Excel set-up at this site. I don't know what that is.

WZTMPL.XLA is a Template Wizard with Data Source Tracker. I have now removed it, but the same behaviour occurs (F8 OK, F5 not OK).

Do you get errors or just nothing happening at all with f5?

With F5 I get the template workbook opened, but the move just doesn't happen.  So after running the macro you can see the template still open, but not moved into the target workbook. I've tried adding in a dummy sheet on the assumption it doesn't like leaving nothing behind (clears the object or whatever) but that didn't work either.

Bit of a long shot, but what if you put a DoEvents line in between the Open and the Move lines?

Thanks, rorya; I did that; still the same; works with F8, doesn't work with F5 or shortcut.
GTG -I'll be offline for a few hours now.

It gets weirder. If I put a stop command into rorya's code, after opening the workbook and running with a shortcut or F5, I don't ever hit it. In other words, processing stops after opening the file without an error message.

It looks now like it's nothing to do with move or copy at all - it's all to do with workbooks.open failing without an error trap.

Question: does it work if you run the routine from the macros dialog, not from f5 or from a shortcut key?

Yes, it does. That's weird. What does that mean?

Damned if I know! :)

I was doing some research and came across a similar problem posted elsewhere and the final post mentioned that particular oddity, so I thought I'd check. Might also help narrow it down for further research. If nothing else, it gives me more details if I need to file this as a bug (I wouldn't hold your breath for a fix though!).

Rory

This code (now running on a different machine, with Excel 2003/VB 6.5 this time) will run successfully to completion using F5 as well as F8 and the Macro dialog box, but, using a shortcut, drops out in the same way before the first Stop.

David

Sub Test2()
    ChDir "C:\Temp"
    Workbooks.Open Filename:="C:\Temp\target.xls"
    Stop
    Workbooks.Open Filename:="C:\Temp\DataSourceTemplate.xls"
    Stop
    Sheets("DataSource").Copy Before:=Workbooks("target.xls").Sheets(1)
    Rows("2:2").ClearContents
    Windows("DataSourceTemplate.xls").Close
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Does it matter what shortcut you use?

The most innocuous question gets a solution...  YES it does seem to matter. Upper case shortcut keys drop out, while lower case shortcut keys work.  That is, on a sample of two. Control-shift-U and Control-shift-T  fail, whereas Control-u and Control-a work.  What kind of bug is that?

That's really helpful, and as far as I'm concerned it's a solution - my client presses Control-u instead of Control-U, hey presto. However, pedants would say the problem I originally raised hasn't actually been resolved - after all, it dropped out after  Workbook.Open with F5, not shift F5.  So that part of the question is still open.

All the same, I'm accepting the solution for all practical purposes.  Thanks for your help!

Regards,

David

This resolves shortcuts not working but not F5 failing when F8 works.   This is less important as F5 is a developer tool, whereas the shortcuts are user tools.