Excel VBA - Better way to filter and retrieve data from another worksheet

Question

This may be too broad of a question and I apologize as I've seen various references as to how to do this but not comprehensively, anyway...

I've been building macros in Excel for the first time over the last three weeks. I have been using the record macro feature as I learn how to hard code and things have been running successfully. That said, I know there's a better/faster/more efficient way to do what I'm doing.

Essentially, I'm building the macros to update a main dashboard workbook with data from a number of other individual workbooks (controlled by various parts of the organization). The code I've been using looks like this - and is what the macro recorder used, which I mimicked.

Sub OperatingCostUpdate()

' ' OperatingCostUpdate Macro ' Pulls the operating cost information from external data connection workbook and moves it into the Operating Cost tab for monthly reporting. '

'Declares community variable

Dim db_community As String

'Pulls community name for use in filter

Windows("Dashboard Main Macro.xlsm").Activate
Sheets("Community Information").Select

If Range("A4") <> "" Then
    db_community = Range("A4")
End If

'Filters sheet based on community

Windows("Billing Attributes With Unit Attributes.xlsx").Activate
ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
    :=1, Criteria1:=Array(2, db_community)

'Declares PM account number variable

Dim db_pmaccount As String

Windows("Dashboard Main Macro.xlsm").Activate
Sheets("Community Information").Select

If Range("C4") <> "" Then
    db_pmaccount = Range("C4")
End If

'Filters based on PM account number

Windows("Billing Attributes With Unit Attributes.xlsx").Activate
ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
    :=2, Criteria1:=Array(2, db_pmaccount)

'Filters based on property type, sets to apartments only

ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
    :=12, Criteria1:="Apartment"

'Delcares date variable

Dim db_date As Date

'Pulls date for selected community for use in filter

Windows("Dashboard Main Macro.xlsm").Activate
Sheets("Community Information").Select

If IsDate(Range("F4")) Then
    db_date = Range("F4")

End If

Windows("Billing Attributes With Unit Attributes.xlsx").Activate
ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
    :=10, Operator:=xlFilterValues, Criteria2:=Array(2, db_date)

'Filters out negative values in calculation columns

ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
    :=6, Criteria1:=">=0", Operator:=xlAnd
ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
    :=7, Criteria1:=">=0", Operator:=xlAnd
ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
    :=9, Criteria1:=">=0", Operator:=xlAnd

'Pulls data and inserts into report for selected community

Windows("Dashboard Main Macro.xlsm").Activate
Sheets("REPORT").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = _
    "=SUBTOTAL(109, '[Billing Attributes With Unit Attributes.xlsx]BillingAttributes_2YRs'!C7)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
    "=SUBTOTAL(109, '[Billing Attributes With Unit Attributes.xlsx]BillingAttributes_2YRs'!C6)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
    "=SUBTOTAL(109, '[Billing Attributes With Unit Attributes.xlsx]BillingAttributes_2YRs'!C9)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])/'Community Information'!R[2]C"
Range("B2:E2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Is there a better way to both filter and copy this data and move it into the "dashboard" workbook?

THANKS!

Tim Williams Tim Williams · Accepted Answer · 2014-11-19T22:02:18

Gernerally speaking there's nothing wrong with your code, but you will find over time your code is difficult to maintain without using a few variables to refer to your commonly-used items.

E.g: something like this is easier to fix or modify

Sub TT()
'Declares community variable

    Dim db_community As String
    Dim db_pmaccount As String
    Dim db_date As Date

    Dim shtCI As Worksheet, shtBill As Worksheet, shtReport As Worksheet
    Dim tblBA As ListObject

    Set shtCI = Workbooks("Dashboard Main Macro.xlsm").Sheets("Community Information")


    Set tblBA = Workbooks("Billing Attributes With Unit Attributes.xlsx") _
                .Sheets("sheetName?").ListObjects("Table_BillingAttributes_2YRs")

    'Filters sheet based on community
    db_community = shtCI.Range("A4").Value 'Pulls community name for use in filter
    tblBA.Range.AutoFilter Field:=1, Criteria1:=Array(2, db_community)

    'Filters based on PM account number
    db_pmaccount = shtCI.Range("C4").Value
    tblBA.Range.AutoFilter Field:=2, Criteria1:=Array(2, db_pmaccount)

    'Filters based on property type, sets to apartments only
    tblBA.Range.AutoFilter Field:=12, Criteria1:="Apartment"

    If IsDate(shtCI.Range("F4").Value) Then
        db_date = shtCI.Range("F4").Value
    End If

    tblBA.Range.AutoFilter Field:=10, Operator:=xlFilterValues, _
                             Criteria2:=Array(2, db_date)

    tblBA.Range.AutoFilter Field:=6, Criteria1:=">=0", Operator:=xlAnd
    tblBA.Range.AutoFilter Field:=7, Criteria1:=">=0", Operator:=xlAnd
    tblBA.Range.AutoFilter Field:=9, Criteria1:=">=0", Operator:=xlAnd

    'etc etc

End Sub

Excel VBA - Better way to filter and retrieve data from another worksheet

1 Answers