Hello elsuji,
This worked for me.
Private Sub CommandButton1_Click()
Dim Area As Variant
Dim DstWkb As Workbook
Dim DstRng As Range
Dim DstWks As Worksheet
Dim row As Long
Dim Rng As Range
Dim SrcWkb As Workbook
Dim SrcRng As Range
Dim SrcWks As Worksheet
Set DstWkb = ThisWorkbook
Set DstWks = DstWkb.Worksheets("Test")
Set DstRng = DstWks.Range("A150")
On Error Resume Next
Set SrcWkb = Workbooks("Consolidated History.xlsx")
If Err <> 0 Then
MsgBox "Please Open the workbook ""Consolidated History.xlsx""", vbCritical
Exit Sub
End If
Set SrcWks = SrcWkb.Worksheets("Consolidated History")
Set SrcRng = SrcWks.Range("A1").CurrentRegion
On Error GoTo 0
With SrcWks
.AutoFilterMode = False
.UsedRange.AutoFilter Field:=4, Criteria1:=DstWks.Range("C2").Value, VisibleDropDown:=True
End With
Set Rng = SrcWks.UsedRange.SpecialCells(xlCellTypeVisible)
For Each Area In Rng.Areas
For row = 1 To Area.Rows.Count
Area.Copy DstRng
Set DstRng = DstRng.Offset(Area.Rows.Count, 0)
Next row
Next Area
End Sub