BRI this is reply to BRI
you should have opened a new thread.
I have multiple worksheets within the workbook that I would like to link to the "CURRENT" worksheet
This is not clear. I am rephrasing sit. See whether I reflect your ideas.
There are many sheet let us gallaher1, gallaher 2 etc with yes or no in column D of each of these sheets. There is also another unique sheet called "current". You have filter out the rows containing "no" in column D of gallaher sheets and copy it to the now empty sheet current.
I have configured a sample workbook which you can download from the following web page.
The sheet current is after running the macro "test"
For rechecking you can again run the macro "test". It clears the entries in "current" and again fills its up with filtered data.
When writing the criteria in the macro the spelling and the case should be correct.
The macro is
Dim r As Range, dest As Range, j As Integer, k As Integer
Dim r1 As Range
j = Worksheets.Count
For k = 1 To j
If Worksheets(k).Name = "Current" Then GoTo nextk
Set r = .UsedRange
r.AutoFilter field:=4, Criteria1:="No"
Set r1 = r.Offset(1, 0)
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)