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?
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.
"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:
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.
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