(Solved) – Grouping values in a variable?

  • by
(solved)-–-grouping-values-in-a-variable?

If I had a file I wanted to cut into a bunch of files based on column A values, would it be possible to put certain values into an array to group as one file–instead of separate files?

for example, I’d like to do something like this to group, but I don’t know how to craft the IF statement when the array prints to the template I’m using?

  Dim SharedServices As Variant
  SharedServices = "Manager 1, Manager 3, Manager 5, Manager 9"

and then

    If Data(i, 1) <> SharedServices Then

        For sourceCol = 1 To UBound(Data, 2)
          Dest.Offset(destRow, destCol) = Data(sourceRow, sourceCol)
          destCol = destcol   1
        Next

        destRow = DestRow   1
    Else
    'Group SharedServices into one file
    End If
    Next

I can’t quite figure out how to establish the grouping of the SharedServices array into one file

script:

Option Explicit

Sub Main()
  Dim wb As Workbook
  Dim Data, Last, Login
  Dim sourceRow As Long, destRow As Long, sourceCol As Long, destCol As Long
  Dim Dest As Range
  Dim SharedServices As Variant
  SharedServices = "manager 1, manager 3, manager 7, manager 11"
  Set wb = Workbooks("Template.xlsx")

  Set Dest = wb.Sheets("Full Roster").Range("A3")

  With ThisWorkbook.Sheets("Full Roster")
    Data = .Range("DC3", .Range("A" & Rows.Count).End(xlUp))
  End With
  wb.Activate
  Application.ScreenUpdating = False

  For sourceRow = 1 To UBound(Data)

    If Data(sourceRow, 1) <> Last Then

      If sourceRow > 1 Then

        Dest.Select

        wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
          ValidFileName(Last & " - Validation.xlsx")
      End If

    With Sheets("Exempt Population")
        .Rows(3 & ":" & .Rows.Count).ClearContents
    End With

      Last = Data(sourceRow, 1)
      destRow = 0
    End If

    destCol = 0

    If Data(sourceRow, 1) <> SharedServices Then

        For sourceCol = 1 To UBound(Data, 2)
          Dest.Offset(destRow, destCol) = Data(sourceRow, sourceCol)
          destCol = destCol   1
        Next

        sourceRow = sourceRow   1
    Else
    'Group SharedServices into one file
    End If
    Next



SaveCopy wb, Login, Last '<< save the last report


End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *