Forum Discussion

Copper Contributor

Mar 18, 2019

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 n...

excel

Macros and VBA

GiraffeCat

Copper Contributor

Mar 20, 2019

PeterBartholomew1

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

PeterBartholomew1

Silver Contributor

Mar 20, 2019

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

GiraffeCat
Copper Contributor
Apr 08, 2019
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