Many years back we had to come up with a solution to survey results that we were getting via CSV. Back then we'd get data where the first column was e-mails, and subsequent columns were 1's or null to indicate interest in an organization. We were trying to come up with a solution that went through each column AFTER the e-mail column, and save out in separate workbooks a list of emails for each column that had a 1 in them so we could send it to those organizations.
Our data (simplified) looked like this:
Where the end result would provide 4 new .xlsx files (club1.xlsx, club2.xlsx, club3.xlsx,etc), each with the 'emails' that had 1's in their row for their respective column. (In the above example Club1.xlsx would have Email1, Email3, Email7 listed)
At the time, the StackExchange community was super helpful in helping us source a solution by providing the following VBA code to run a macro:
Option Explicit Sub FilterData() Dim Responses As Worksheet Dim Column As Long Set Responses = ThisWorkbook.Worksheets("Responses") Column = 2 Do While Responses.Cells(1, Column).Value <> "" With Workbooks.Add(xlWBATWorksheet) With .Worksheets(1) Responses.Cells.Copy .Cells .Columns(Column).AutoFilter Field:=1, Criteria1:="<>1" .Rows(2).Resize(.Rows.Count - 1).Delete Shift:=xlUp .Columns(2).Resize(, .Columns.Count - 1).Delete Shift:=xlShiftToLeft End With .Close SaveChanges:=True, Filename:=ThisWorkbook.Path & "\" & Responses.Cells(1, Column).Value End With Column = Column + 1 Loop End Sub
But our layout has since changed and for the life of us we can't figure out how to modify the code to include more columns in the save out. Instead of just having the 'Email' column, we now have additional columns for Preferred Name, First Name, Last Name, and Pronouns. Our attempts at modifying the above code has only served to either break the macro entirely, or only save out a single row.
Would anyone have and advice on to how we could either write new code, or modify the existing code to include all the columns in our exports (so Club1.xlsx would now have columns/row data for referred Name, First Name, Last Name, Pronouns, and Emails for each column with a "1").
Any thoughts? I'm stumped.