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"
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
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