differences in "live" mode v. "debug" mode for macro

[dnorris]

I wrote a macro about a year ago that worked just fine. I've had some upgrades to Excel since then and now it doesn't work.

I've debugged the problem to this. Here is the code.

Set Dest = Application.InputBox(prompt:="Where do you want the value pasted?" & Chr(10) & Chr(10) & "Rounded numbers will be pasted as values, and cannot be undone.", Title:="Select Destination", Type:=8)
Sheets("temp sigfig sheet").Activate
Range("A1").Activate
Range(Selection, Selection.End(xlDown)).Copy
Sheets(tempsheet).Activate
Dest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("temp sigfig sheet").Delete

Info - tempsheet is the Worksheet name where I want the values pasted. It is different than "temp sigfig sheet".
I have dimensioned Dest as a Range.
The values to copy/paste are located on the "temp sigfig sheet" in a column of varying length starting at "A1".

If I run the macro from an Excel workbook, it pastes the values on the "temp sigfig sheet". Not what I want. I want them on tempsheet.
If I set a Breakpoint in the macro at the first line above, then run the macro and continue from the Debugger after it stops, it correctly pastes the values on the tempsheet Worksheet.

Any idea what the problem is? I have run this on a different computer (newer) and it works fine. Is there an update I need?

 

[Norie]

I don't know if it will solve the problem, but you should lose the Activate and qualify the references.

But I'm afraid it's actually unclear what you actually want to do.

One problem is that you don't seem to be giving a value to 'tempsheet' anywhere in the code.

And I'm not sure about your use of Dest.

Perhaps something like this.

Set Dest = Application.InputBox(prompt:="Where do you want the value pasted?" & Chr(10) & Chr(10) & "Rounded numbers will be pasted as values, and cannot be undone.", Title:="Select Destination", Type:=8)
With Sheets("temp sigfig sheet")
    LastRow = .Range("A1").End(xlUp).Row
    .Range("A1:A" & LastRow).Copy
End With
Sheets("tempsheet").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.DisplayAlerts = False
Worksheets("temp sigfig sheet").Delete
Application.DisplayAlerts = True

 

[dnorris]

Let me clarify what I'm trying to do.

Earlier in the code, I've done some calculations on the "temp sigfig sheet" and the final values I need are located in a column of varying length starting at "A1". I'm using an Inputbox to allow the user to indicate where they want the final values pasted.

Dest is dim as a Range and the output for the InputBox.

tempsheet is dim as a string and the name of the Worksheet where I want the values pasted. The InputBox appears while the tempsheet Worksheet is active and visible to the user. They select the range (or a single cell where the paste vals should begin) and click "OK".

The code I posted works fine, if I set a breakpoint at the "Set Dest = ... " line of code, and then continue in the Debugger. If I run it from a workbook without the breakpoint, it pastes the values on the wrong worksheet.

Thanks for your suggestions, I'll try the different code for PasteValues.

 

[Norie]

I kind of guessed what you are trying to do.

But I still don't see anywhere that you give a value to 'tempsheet' in the posted code.:eek

Perhaps I'm missing something, or that's not all the code you have.

 

[dnorris]

The first 2 lines of code:

Dim tempsheet As String
tempsheet = ActiveSheet.Name

Then a long set of code that works fine that does calculations and moving data around. It isn't important to the problem I'm having.

Then

Dim Dest As Range

Then the code I posted before.

I've done some additional debugging...

It seems if I run the macro without a Breakpoint, the "Set Dest =..." does not return the correct Range into Dest. I know this because if I set a Breakpoint before the "temp sigfig sheet" gets deleted, I can look and see that is where the values are pasted. If I set a Breakpoint, and continue from the Debugger, the correct Range gets returned to Dest and the values get pasted on tempsheet in the Range I want.

It almost seems like I am running different InputBox code in the Debugger when I set a breakpoint, than when a Breakpoint isn't set...

Any ideas?