Forum Discussion
Macro to find Data, copy the entire row?
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.
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
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
"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.
