Excel VBA_Copy Range_Filtered

BellBell

New Member
Joined
Apr 30, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
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

New Member
Joined
Apr 30, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Try this

VBA Code:
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!
 

Attachments

  • Excel_Match Headers_V1.PNG
    Excel_Match Headers_V1.PNG
    12.9 KB · Views: 2

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 30, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

Attachments

  • Excel_Match Headers_V1.PNG
    Excel_Match Headers_V1.PNG
    12.9 KB · Views: 2
  • Excel_Match Headers_V2.PNG
    Excel_Match Headers_V2.PNG
    3.4 KB · Views: 2

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 30, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Attachments

  • Excel_Match Headers_V1.PNG
    Excel_Match Headers_V1.PNG
    31.9 KB · Views: 0
  • Excel_Match Headers_V2.PNG
    Excel_Match Headers_V2.PNG
    14.5 KB · Views: 0

Watch MrExcel Video

Forum statistics

Threads
1,129,697
Messages
5,637,868
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top