VBA with multiple selection.copy not registering

CaulfieldS · Jul 9th 2020

I have a macro that takes the current selection, copies it, paste's it in a certain cell, copies it again (unsure if that is necessary) paste's again forming a list then its supposed to copy a range that i paste outside the program (non Microsoft program). The issue im running into is that in excel it shows the final range selected and copied (Green boarder with white "ants" circling around) however when i switch programs to paste the data, the original cell i copied is whats pasted.

Code

Sub SwitchRiderForTimes()
Dim numrows As Long
' SwitchRiderForTimes Macro
Application.ScreenUpdating = False
    Selection.Copy
    Range("AP2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("AP2").Select
Selection.Copy
Range("An1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
numrows = Range("aP1")
Range("D1:D" & numrows).Select
Selection.Copy
End Sub

Display More

**royUK** · Jul 9th 2020

If you switch ScreenUpdating off then you mist restore it.

It's not exactly clear what you are doing, but try this

Code

Sub SwitchRiderForTimes()
' SwitchRiderForTimes Macro
With Application
.ScreenUpdating = False
Selection.Copy
Range("AP2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AP2").Copy
Range("An1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.CutCopyMode = False
Range("D1:D" & CInt(Range("aP1").Value)).Copy
.ScreenUpdating = True
End With
End Sub

Display More

CaulfieldS · Jul 9th 2020

thank you RoyUK. It is now retaining the right information on the clipboard a third of the time during my tests. If i revise line 12 of your code "copy" to "select" then manually "Ctrl+C" then switch programs and paste it works flawlessly the entire time

**royUK** · Jul 9th 2020

You don't need to select. I'll see what I can add to the code

CaulfieldS · Jul 10th 2020

RoyUK correct, if it didnt glitch then the "select" wouldn't be needed. however to diagnose where it's having its issue i replaced "copy" with "select" then after the macro has run i "Ctrl+C" then paste elsewhere and it works just fine. Which leads me to understand there is an issue with the last "copy" command. The weird part is that its not consistent, some instances the final copy command works, and some not.

**royUK** · Jul 10th 2020

Is all the data on one sheet?

CaulfieldS · Jul 10th 2020

The data all comes from various places in the same workbook. The cell's being copied are formula's. Could that have something to do with it? If I add to the end of the macro to paste the copied range into a blank area of the same worksheet it works no problem.

**royUK** · Jul 10th 2020

You need to use the sheet's name if your data is on multiple sheets. Also, do you need to paste formulas or values.

Code

Sheets("this sheet name").Range("AP2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Can you attach an example workbook and let me know on which sheet each range is.

VBA with multiple selection.copy not registering

Participate now!

Share