Copy Selected Names To A Different Sheet

[cmangi]

Looking for some help creating an macro to automate a task.

The excel file I am working with contains two (2) tabs: 1) Upload Sheet and 2) Database Sheet. The Database Sheet contains a large list of names (reference column B in example below). We use Column A to select the desired name (reference "X"s in example below) to be transferred to the Upload Sheet.

Looking for a macro that will copy all names in Column B identified with a non-blank cell in Column A of the Database Sheet to Column A of the Upload Sheet. See example below

Ideally there are no blank rows in the Upload Sheet after copying.

DATA BASE SHEET

	COLUMN A	COLUMN B
Row 3	X	John
Row 4		Frank
Row 5	X	Bill

UPLOAD SHEET AFTER MACRO Result

	COLUMN A
Row 2	John
Row 3	Bill

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = "X")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"})
in
    #"Removed Columns"

 

[DanteAmor]

Try this

Sub Macro1()
  Dim lr As Long
  With Sheets("Database")
    If .AutoFilterMode Then .AutoFilterMode = False
    lr = .Range("B" & Rows.Count).End(3).Row
    .Range("A2:B" & lr).AutoFilter Field:=1, Criteria1:="<>"
    .Range("B3:B" & lr).Copy Sheets("Upload").Range("A2")
    .ShowAllData
  End With
End Sub