Filtering to a New sheet

[chiswickbridge]

I have a database in a sheet named All_Ports from A2 to G9999.


Port / Locode / Country Code / Country / PortCode / Status / Nationality


Column F ( Status ) has only 2 values, YES or NO.


Need a macro to sort Column F and select all YES only and copy/ paste to another sheet named Current_Ports.


Every week, the status YES keep changing, so my sheet Current_Ports will be up-to-date with te click of a singly macro.


Wishing you all A happy New Year 2017

 

[AliGW]

Have you tried the record macro option for this? You could then assign the macro to a button.

 

[vcoolio]

Hello Chiswickbridge,

You could try the following code assigned to a button:-

Sub TransferData()
Dim lr As Long
Application.ScreenUpdating = False
Sheet1.Range("F1", Sheet1.Range("F" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Yes", 7
  lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    If lr > 1 Then
      Sheet1.Range("A2", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp)).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
          Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp)).EntireRow.Delete
             Sheet2.Columns.AutoFit
         End If
Sheet1.[F1].AutoFilter
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

The code filters Column F for the criteria "Yes" and transfers the relevant rows of data to sheet 2. It then deletes the transferred data from sheet1.

Following is the link to my test work book. Click on the "RUN" button to see it work:-

https://www.dropbox.com/s/t5bg1ry6v...om sht1 to sht2, autofilter) - Copy.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

 

[chiswickbridge]

Thanks...vcoolio... does the job perfectly...

 

[vcoolio]

You're welcome Chiswickbridge. I'm glad that I was able to help.

Cheerio,
vcoolio.