Excel VBA_Copy Range_Filtered

[BellBell]

Hi, i would like to copy data (with filtered) to another sheet. But unable to do so; either no data was copy / the data wasn't filtered.
In tab Case Details, i want to filter the data at column F with "Update to progress" at first, before i start copy selected range to another new sheet.

Below is the VBA i get from previous post i've read and i try it by myself. Please guide me if any:

Sub AutoFilter_RangeCopy_Row()

' Get the worksheets
Dim shRead As Worksheet, shWrite As Worksheet
Set shRead = ThisWorkbook.Worksheets("Case Details")
Set shWrite = ThisWorkbook.Worksheets("Pivot")

Dim lastCol As Long

' Get the range
Dim rg As Range
Set rg = shRead.Range("F1").CurrentRegion

' Remove any existing filters
rg.AutoFilter

' Apply the Autofilter
rg.AutoFilter Field:=6, Criteria1:="*Update to progress*"

' Copy Range & Paste Special that match Headers only
Application.ScreenUpdating = False
Dim LastRow As Long, header As Range, foundHeader As Range, lCol As Long, srcWS As Worksheet, desWS As Worksheet
Set srcWS = Sheets("Case Details")
Set desWS = Sheets("Pivot")
LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column
For Each header In desWS.Range(desWS.Cells(1, 1), desWS.Cells(1, lCol))
Set foundHeader = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
If Not foundHeader Is Nothing Then
srcWS.Range(srcWS.Cells(2, foundHeader.Column), srcWS.Cells(LastRow, foundHeader.Column)).Copy desWS.Cells(2, header.Column)
End If
Next header
Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

Try this

Sub AutoFilter_RangeCopy_Row()
  Dim srcWS As Worksheet, desWS As Worksheet, lr As Long
  Dim header As Range, fHead As Range
  
  Application.ScreenUpdating = False
  Set srcWS = ThisWorkbook.Worksheets("Case Details")
  Set desWS = ThisWorkbook.Worksheets("Pivot")
  If srcWS.AutoFilterMode Then srcWS.AutoFilterMode = False
  lr = srcWS.Range("F" & Rows.Count).End(3).Row
  srcWS.Range("A1:F" & lr).AutoFilter Field:=6, Criteria1:="*Update to progress*"
  
  ' Copy Range & Paste Special that match Headers only
  For Each header In desWS.Range("A1", desWS.Cells(1, Columns.Count).End(xlToLeft))
    Set fHead = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
    If Not fHead Is Nothing Then fHead.Offset(1).Resize(lr).Copy header.Offset(1)
  Next header
  Application.ScreenUpdating = True
End Sub

 

[BellBell]

Try this

Sub AutoFilter_RangeCopy_Row()
  Dim srcWS As Worksheet, desWS As Worksheet, lr As Long
  Dim header As Range, fHead As Range

  Application.ScreenUpdating = False
  Set srcWS = ThisWorkbook.Worksheets("Case Details")
  Set desWS = ThisWorkbook.Worksheets("Pivot")
  If srcWS.AutoFilterMode Then srcWS.AutoFilterMode = False
  lr = srcWS.Range("F" & Rows.Count).End(3).Row
  srcWS.Range("A1:F" & lr).AutoFilter Field:=6, Criteria1:="*Update to progress*"

  ' Copy Range & Paste Special that match Headers only
  For Each header In desWS.Range("A1", desWS.Cells(1, Columns.Count).End(xlToLeft))
    Set fHead = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
    If Not fHead Is Nothing Then fHead.Offset(1).Resize(lr).Copy header.Offset(1)
  Next header
  Application.ScreenUpdating = True
End Sub

Hey Dante! Thank you for replying!
I've tried but it dont copy according to "match headers" that i need in different sheet.
Attached image for your reference.

- i would like to filter column F by "update to progress" then,
- copy range (or selected headers) to another sheet (Pivot), lets say column Food and column Name

Thank you for your feedback in advance!

 

[DanteAmor]

I am confused, according to your macro the headers are in row 1, but according to your image the headers are in row 3, you can tell me.
You can put an image of your 2 sheets.

 

[BellBell]

Sorry Dante for the confusion i have made.
1) Attached image V1 from source data i need to copy from (sheet Case Details)
2) Attached image V2 data to paste to (sheet Pivot)

Criteria:

• first of all, filter column F (Case Status) on row 3 by "update to progress" then,
• copy range to another sheet (Pivot)
  • it is not all range to be copy, only selected based on sheet Pivot
  • the headers are from row 2 from sheet Case Details

 

[DanteAmor]

It would be great if your image1 had more examples and you put the results in image2.
It is confusing, because you have, in image 1, headings in row 1, row 2 and row 3, so when to search in row 1, when to search in row 2 and when to search in row 3.

 

[BellBell]

Hey Dante, apologies for late reply.
With wider images and more examples on both images, hope you understand.

1) Attached image V1 from source data i need to copy from (sheet Case Details)
2) Attached image V2 data to paste to (sheet Pivot)

Criteria:

• first of all, on sheet tab Case Details, there are many headers (No, Date, Name, Age, City, Case Status, Code_1, Code_2)
• next, filter column F (header: Case Status) on 3rd row by "update to progress" then,
• all data filtered with "update to progress" will be transferred to sheet tab Pivot,
  • not all data will be transferred, it is based on headers in sheet tab Pivot
  • in sheet tab Pivot, i only need all data with match headers from sheet tab Case Details (Name, Age, Food, Date)