CODEWITHSUNDEEP
vbaexcel
messi1335
messi1335

Reputation: 27

Excel VBA - unselect range after paste with code using variables

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

Upvotes: 2

Views: 27964

Answers (2)

victor
victor

Reputation: 105

see this, Unselect column after pasting data i ended up selecting "A1" inorder to remove the merquri from selection area

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

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.

Upvotes: 1

Related Questions