Running a Macro that pulls data from a different file

[JC94]

Hello!

I have a very large file, a master file if you will, which includes a lot of data, and is updated regularly. I would like to create a separate file which pulls details from this large file to consistently provide a filtered list of this data which is easier to view, and can allow me to put more detailed data on it as well.

See attached a condensed mock version of my master file, called 'Master File'.

So, in the new file (let's say it will be called 'Gold Members'), I'd like to have a list of just 'Gold' member types which includes all the other data from the master file. Ideally, I'd like this file to update from Master File without having to open Master File.

I've got the following code which puts it into a different sheet in the Master File (which sadly isn't useful for what I need):

Sub Test()

Dim Cell As Range

With Sheets(1)
    ' loop column D until last cell with value (not entire column)
    For Each Cell In .Range("D3:D" & .Cells(.Rows.Count, "D").Row)
        If Cell.Value = "Gold" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .Rows(Cell.Row).Copy Destination:=Sheets(2).Rows(Cell.Row)
        End If
    Next Cell
End With

End Sub

Aside from this being in the same file, the other problem I have is any rows with non-Gold members are copied as blanks. Ideally I'd like it to just copy Gold members and have no blanks between them

Both files will be saved in the same folder / location.

Is anyone able to help me with creating a new code to get this done?