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