Excel VBA - unselect range after paste with code using variables

Question

I wrote a macro to filter, copy and paste the filtered criteria into different workbooks. I cannot figure out how to unselect the range that is being pasted in within the workbooks. I have tried ".range("A1").select" , "application.cutcopymode = false". I do not know what else to try..here is my code - any insight would be helpful

Dim i As Long
Dim market As Variant, arrbooks() As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheet1.AutoFilterMode = False
market = Array(...
 ReDim arrbooks(0 To UBound(market))

'create workbooks
For i = 0 To UBound(market)
    Set arrbooks(i) = Workbooks.Add
Next

'retrieve data by autofilter

With Sheet4

    For i = 0 To UBound(market)

        .Range("H:H").AutoFilter field:=1, Criteria1:=market(i)

        .Range("H1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy

       Workbooks(arrbooks(i).Name).Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteAll


**workbooks(arrbooks(i).name).sheets(1).application.cutcopymode = false**

        Next
End With

'save workbooks  

For i = 0 To UBound(market)

**workbooks(arrbooks(i).name).sheets(1).application.cutcopymode = false**

Workbooks(arrbooks(i).Name).SaveAs "insert save path"
Workbooks(arrbooks(i).Name).Close
Next

'clean up
Application.ScreenUpdating = False
Sheet4.ShowAllData
Sheet4.AutoFilterMode = False
Application.DisplayAlerts = True

Sheet4.Activate
Sheet4.Range("A1").Select

due to compliance issues I cannot disclose the names I am filtering on

Thanks!

edit: **indicates where I tried inserting .application.cutcopymode = false

Would you show how you tried Application.CutCopyMode = False by inserting it again in the code? — A.S.H
I tried it in two places, and the macro ran both times, however when I opened the saved file - the entire pasted section was still selected. Here is both places I tried it. - I edited the original post — messi1335
Type "Application.CutCopyMode = False" only, without all that "workbooks..." prefix — user3598756
cutcopymode.false removes the dotted lines from a section that was setto copy. To unselect a range simply select another, line range("A1").select — John Muggins

A.S.H A.S.H · Accepted Answer · 2017-03-13T17:13:20

Ok I see now what you're trying to do. It has to do with the selection in the destination, not with the CutCopyMode. Try

Application.Goto workbooks(arrbooks(i).name).sheets(1).Range("A1")

OTOH, CutCopyMode removes the "dotted" selection of the source, so that you cannot manually paste it again.

Excel VBA - unselect range after paste with code using variables

2 Answers