Macro to find Data, copy the entire row?

Copper Contributor

I have a large spreadsheet sent to me twice a month. I can record a couple of macros that help me delete the columns I don't need and to sort the data, but I don't know how to proceed from there. I need to find a value in Column F, select all Rows that contain that data, and then copy all of the Rows to a new sheet. If I try to record a macro while doing that via CTRL F, CTRL A, Close, CTRL-, etc. the resulting macro only selects the "Row Numbers" that contain the data, and then goes on from there. Since the actual number of the rows that contain the data will change twice a month it won't work for me.

 

Below is what it generates:

 

Sub Macro910()
'
' Macro910 Macro
'

'
    Rows("981:3588").Select
    Range("F981").Activate
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Range("A1").Select
End Sub

 

Is there a way to get a macro to find the data in Column F, select all of the rows that contain the data, and copy all of the rows to a new sheet?

 

Any help would be greatly appreciated.

5 Replies

@GiraffeCat 

I would recommend using Tables for data transfer between the worksheet and VBA macros.  Table references adjust to match the actual data.

 

Rather than learning VBA, however, you might find it easier and more effective to use the Power Query tools (Get and Transform) on the 'Data' Ribbon Tab.  That will take data into memory from a Table (or even from other files or databases) and it provides all the options you are likely to need for filtering, sorting, appending data sets, merging etc. before loading the result back to a fresh Table.  It will all be very unfamiliar at first but the functionality is purpose-written for the type of work you have described.

@Peter Bartholomew 

 

I have been using Power Query "From Table" since I volunteered to help out. I first sort it out by state, then by the desired city, and then "Close and Load" to a new sheet. I do this for the  10 specific cities need to track.  I was just hoping for a more automated way to do it.

 

I have no idea how to use "Tables for data transfer, so it sounds like I may have to take a class, or ten, to learn VBA.

 

Thank you for your help

@GiraffeCat 

"I was just hoping for a more automated way to do it"

You are almost there, then.  All you need to find now is how to change the data source (a different file to replace the first or different data inserted into a given table) and hit 'refresh'.  All 10 cities at one click!

 

On the VBA front, I would never use a direct cell reference from VBA.  Even using 'macro recorder' style of coding:

"Sub ExtractData()
ActiveSheet.ListObjects("DataTable").Range.AutoFilter Field:=1, Criteria1:= "MA"
Range("DataTable").Select
Selection.Copy
Range("Extract").Select
ActiveSheet.Paste
End Sub"

means that you do not have to change the code each time the table "DataTable" resizes or even moves.  I would also prefer to eliminate 'select' from my code but that would require me to learn more about ways of transferring filtered data to code rather than the underlying dataset.

@GiraffeCat 

Slightly cleaner code

Sub ExtractData()
Dim rng As Range
'Clear the content of the destination Table
Set rng = ActiveSheet.ListObjects("DataExtract").DataBodyRange
rng.ClearContents
'Filter the source Table
ActiveSheet.ListObjects("DataTable").Range.AutoFilter Field:=1, Criteria1:= _
Range("Criterion").Value
'Copy filtered data from source to destination
Range("DataTable").Copy _
Destination:=rng.Item(1)
End Sub

Peter,

So far I have it working with five "recorded" macros - two of which use power query. It is down to about 18 mouse clicks, so I am happy. I will continue to learn how to do this stuff....

Thank you so much for taking the time to help me out.

Dan