title: Excel VBA - Better way to filter and retrieve data from another worksheet
body: <p>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...</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <pre><code>Sub OperatingCostUpdate()
  </code></pre>
  
  <p>'
  ' OperatingCostUpdate Macro
  ' Pulls the operating cost information from external data connection workbook and moves it into the Operating Cost tab for monthly reporting.
  '</p>
  
  <pre><code>'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") &lt;&gt; "" 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") &lt;&gt; "" 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:="&gt;=0", Operator:=xlAnd
  ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
      :=7, Criteria1:="&gt;=0", Operator:=xlAnd
  ActiveSheet.ListObjects("Table_BillingAttributes_2YRs").Range.AutoFilter Field _
      :=9, Criteria1:="&gt;=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
  </code></pre>
  
  <p>Is there a better way to both filter and copy this data and move it into the "dashboard" workbook? </p>
  
  <p>THANKS!</p>