title: vba to copy selected data to multiple sheets based upon criteria
body: <p>I have the code below which copies data from one table to three other existing sheets but I also need to copy selected columns of the data to other sheets as a summary and append to the existing data.</p>
  
  <p>There are 9 columns in the table but I only need copy 4 of these to the other sheet.  Column 5 holds the criteria which will be used to split the data and I need to copy columns 1,2,4 and 8. </p>
  
  <pre><code>Sub SplitData()
  
  Dim wsInput As Worksheet
  Dim wsOutput As Worksheet
  Dim wsNames As Variant
  Dim i As Long
  Dim LastRow As Long
  
  Set wsInput = Sheets("Input Sheet")
  wsNames = Array("Output 1", "Output 2", "Output 3")
  Const FilterColumn = 7
  
  With wsInput
  LastRow = wsInput.Range("C" &amp; Rows.Count).End(xlUp).Row
  
     For i = 0 To UBound(wsNames)
     Set wsOutput = Worksheets(wsNames(i))
     wsOutput.Cells.ClearContents
  
         With wsInput.Range("C105:K" &amp; LastRow)
         .AutoFilter Field:=FilterColumn, Criteria1:=wsNames(i)
         .Offset(0, 0).Copy wsOutput.Range("A2")
         End With
     Next i
  
  End With
  
  End Sub
  </code></pre>
  
  <p>Any assistance is much appreciated.</p>
  
  <p>I use the code below to copy other data in the same workbook to another sheet and am struggling to find a method of combining this with a table of data which needs to be copied and split into multiple sheets.</p>
  
  <pre><code>Sub CopyExpenditure()
  
  Dim wsCopyFrom As Worksheet
  Dim wsCopyExpTo As Worksheet
  
  Set wsCopyFrom = Worksheets("Input Sheet")
  Set wsCopyExpTo = Worksheets("Cash Expenditure List")
  
  LastRow = wsCopyExpTo.Range("C" &amp; Rows.Count).End(xlUp).Row
  
  wsCopyFrom.Activate
  If wsCopyFrom.Range("F59") = 0 Then
  Exit Sub
  End If
  
  If wsCopyFrom.Range("G59") &gt; 0 Then
      MsgBox ("Row(s) contain a Description but not an amount. Please enter an amount or clear the Description field")
  Exit Sub
  End If
  
  wsCopyExpTo.Activate
  If Range("c" &amp; LastRow) = "Total" Then
  Rows(LastRow).EntireRow.Select
  Selection.Delete Shift:=xlUp
  
  End If
  
  LastCopyRow = wsCopyFrom.Range("C57").Row
  
  For x = 37 To LastCopyRow
  wsCopyFrom.Activate
  LastRow2 = wsCopyExpTo.Range("C" &amp; Rows.Count).End(xlUp).Row
  ActiveSheet.Range("c37:F57").Select
      chkValue = Cells(x, 6).Value
      If chkValue &gt; 0 Then
      Cells(x, 3).Resize(1, 4).Copy
  wsCopyExpTo.Activate
  ActiveSheet.Range("C" &amp; LastRow2 + 1).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  End If
  Next x
  
  LastRow4 = wsCopyExpTo.Range("A" &amp; Rows.Count).End(xlUp).Row
  
  wsCopyFrom.Activate
  ActiveSheet.Range("d7").Select
  Selection.Copy
  wsCopyExpTo.Activate
  ActiveSheet.Range("a" &amp; LastRow4 + 1).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  ActiveSheet.Range("A10:A" &amp; LastRow4 + 1).NumberFormat = "dd/mm/yyyy"
  
  wsCopyFrom.Activate
  ActiveSheet.Range("F7").Select
  Selection.Copy
  wsCopyExpTo.Activate
  ActiveSheet.Range("b" &amp; LastRow4 + 1).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  Application.CutCopyMode = False
  
  LastRow3 = wsCopyExpTo.Range("c" &amp; Rows.Count).End(xlUp).Row
  
  wsCopyExpTo.Range("a" &amp; LastRow2 + 1).Select
  Range("a" &amp; LastRow4 + 1 &amp; ":a" &amp; LastRow3).FillDown
  
  wsCopyExpTo.Range("b" &amp; LastRow2 + 1).Select
  Range("b" &amp; LastRow4 + 1 &amp; ":b" &amp; LastRow3).FillDown
  
  
  Range("G10:AB10").Select
  Range("A10").EntireRow.Hidden = True
  Selection.AutoFill Destination:=Range("G10:AB" &amp; LastRow3), Type:=xlFillDefault
  
  wsCopyExpTo.Cells(LastRow3 + 2, 3) = "Total"
  wsCopyExpTo.Cells(LastRow3 + 2, 6) = WorksheetFunction.Sum(Range("f11:f" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 7) = WorksheetFunction.Sum(Range("g11:g" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 8) = WorksheetFunction.Sum(Range("h11:h" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 9) = WorksheetFunction.Sum(Range("i11:i" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 10) = WorksheetFunction.Sum(Range("j11:j" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 11) = WorksheetFunction.Sum(Range("k11:k" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 12) = WorksheetFunction.Sum(Range("l11:l" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 13) = WorksheetFunction.Sum(Range("m11:m" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 14) = WorksheetFunction.Sum(Range("n11:n" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 15) = WorksheetFunction.Sum(Range("o11:o" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 16) = WorksheetFunction.Sum(Range("p11:p" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 17) = WorksheetFunction.Sum(Range("q11:q" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 18) = WorksheetFunction.Sum(Range("r11:r" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 19) = WorksheetFunction.Sum(Range("s11:s" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 20) = WorksheetFunction.Sum(Range("t11:t" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 21) = WorksheetFunction.Sum(Range("u11:u" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 22) = WorksheetFunction.Sum(Range("v11:v" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 23) = WorksheetFunction.Sum(Range("w11:w" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 24) = WorksheetFunction.Sum(Range("x11:x" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 25) = WorksheetFunction.Sum(Range("y11:y" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 26) = WorksheetFunction.Sum(Range("z11:z" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 27) = WorksheetFunction.Sum(Range("aa11:aa" &amp; LastRow3))
  wsCopyExpTo.Cells(LastRow3 + 2, 28) = WorksheetFunction.Sum(Range("ab11:ab" &amp; LastRow3))
  
  With wsCopyExpTo.Rows(LastRow3 + 2)
      .Font.ColorIndex = 1
      .Font.Bold = True
      .NumberFormat = "_-#,###.00_-;_-(#,###.00);0.00_-"
  End With
  
  Range("AB10").EntireColumn.Hidden = True
  
  
  
  End Sub
  </code></pre>
  
  <p>Would this be best split into two tasks; the first to split the data onto three temporary sheets and the second task to get it into the required format?</p>