Copy / PasteSpecial switches to other sheet

[natefarm]

The code below switches to Outsheet as soon as it hits the PasteSpecial line. I can make it work by adding "Sheets("InSheet").Select" after each PasteSpecial, but is that really necessary? Oddly, if I step through with the debugger, it works fine, but as soon as I hit F5, it switches sheets.

Please Login or Register  to view this content.

[xlnitwit]

Hi,

If all you need is the values you can assign them directly

Please Login or Register  to view this content.

[jeffreybrown]

It switches to the Outsheet on that line because that's where it is qualified.

Please Login or Register  to view this content.

The line in blue is qualified to the Sheets("OutSheet"), but the line before it (.copy) does not have a qualification. It will copy from the active sheet.

[natefarm]

I understand that, Jeff. It is written as intended. But can't it paste to another sheet without actually selecting it?

[simarui]

i attempted to replicate in excel 2010 and 2016 with a very simple macro... neither seemed to have that "feature"... but you might try

Please Login or Register  to view this content.

at the beginning of the code?

[natefarm]

Thanks simarui, but I'm already doing that earlier. I just included the code with the problem.

[jeffreybrown]

But can't it paste to another sheet without actually selecting it?

Correct, you do not have to select the sheet to work with it. You just need to qualify where you want the output to show up.

Did you try Don's suggestion?

[natefarm]

Not yet. I was hoping to figure out why mine didn't work first. Apparently it should. I tried this in a new workbook, copying from Sheet1, and it works fine, so there must be something I'm missing.

Please Login or Register  to view this content.

[natefarm]

Well, here's an odd thing. Below is a condensed version of the full code:

Please Login or Register  to view this content.

If I click in the LoadData subroutine and hit F5, it runs fine. I have a Command Button on a sheet assigned to LoadData, and when I click it, it doesn't work -- as soon as it hits the PasteSpecial line, it switches to OutSheet and stays there. I've proven this by putting a debug stop on the OutRw = OutRw + 1 line. When run directly, it's still in InSheet, but when run with the button, it's in OutSheet.

[jeffreybrown]

I quite honestly don't know why it's behaving that way for you.

Maybe you can fully qualify the sheets, that is, both sheets...

Something like...

Please Login or Register  to view this content.

[xlnitwit]

What kind of button is it and on which sheet is it located?

[natefarm]

I had actually already tried that, Jeff, but good suggestion.

It is a Form control button on a different sheet that is assigned to LoadData in Module1. I had also tried moving the button to InSheet to no avail. So I switched to an ActiveX control, moved the code from LoadData to the click event, and it now works!!?

Go figure. Thanks for your helps.