XL VBA to Filter on 1 Criteria and Cut and Paste into Another Sheet

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance.

I would like to filter on one criteria and cut the rows and paste into another sheet. Since I am a novice at VBA and I have other code that this will be added to, please modify versus just introducing entirely new code. Once again thanks.

VBA Code:
Sub Test()


'________________________________________________________________________________________________________
'Turn off alerts, screen updates, and automatic calculation
    'Turn off Display Alerts
        Application.DisplayAlerts = False

    'Turn off Screen Update
        Application.ScreenUpdating = False

    'Turn off Automatic Calculations
        Application.Calculation = xlManual


'________________________________________________________________________________________________________
'Dimensioning
   
    'Dim long
        Dim i As Long
       
        Dim ColNum As Long
       
        Dim LR_of_Col_T As Long
        Dim LR_of_Col_S As Long
   
    'Dim Strings
        Dim SN_Ticker As String
        Dim SN_Sympathy As String
           
    'Dimensioning Sheets
       

'________________________________________________________________________________________________________
'
   
'3. Move Sympathy tickers (column A will have a "S") from the "Tickers" Tab and place them into the _
    "Sympathy" tab
    '01. Activate the "Tickers" Sheet
        Sheets("Tickers").Activate
       
       
    '02. Get the LastRow of the Column A within the "Tickers" and "Sympathy" Tabs
        '001. Tickers Tab
            SN_Tickers = "Tickers"
            ColNum = 1
            LR_of_Col_T = LastRowColF(SN_Tickers, ColNum)
               
        '002. Sympathy Tab
            Worksheets("Sympathy").Activate
            SN_Sympathy = "Sympathy"
            ColNum = 1
            LR_of_Col_S = LastRowColF(SN_Sympathy, ColNum)
       
       
    '03. Cut Rows with "S" in column A and move to "Sympathy" tab
        '001. Apply Filter
            Worksheets("Tickers").Activate
            Worksheets("Tickers").Range("A6:AA" & LR_of_Col_T).AutoFilter Field:=1, Criteria1:="S"
 
        '002. Cut
            On Error GoTo NoSympathyCut
                Worksheets("Tickers").Range("A6:AA" & LR_of_Col_T).SpecialCells(xlCellTypeVisible).Select
                Worksheets("Tickers").Range("A6:AA" & LR_of_Col_T).SpecialCells(xlCellTypeVisible).Cut
                Worksheets("Sympathy").Activate
                Worksheets("Sympathy").Range("A" & LR_of_Col_S + 1).Select
                Worksheets("Sympathy").Range("A" & LR_of_Col_S + 1).Paste
NoSympathyCut:
            Resume Next

        '003. Clear Filter
            Worksheets("Tickers").Activate
            On Error Resume Next
                Worksheets("Tickers").ShowAllData
            On Error GoTo 0
       
'________________________________________________________________________________________________________
'Turn on alerts, screen updates, and calculate

        'Turn On Display Alerts
            Application.DisplayAlerts = True

        'Turn on Screen Update
            Application.ScreenUpdating = True

        'Turn off Automatic Calculations
            Calculate

End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,129,363
Messages
5,635,836
Members
416,885
Latest member
hs11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top