CODEWITHSUNDEEP
excelvba
Cody S
Cody S

Reputation: 87

Copy/Export Multiple Columns

We have a form to indicate interest in various clubs. The output logs the data in an Excel spreadsheet with columns for their preferred name, last name, email, pronouns, and a "1" in the corresponding column for the clubs they are interested in (mock-up below).

We want to provide clubs with their own file containing the respondents based on their '1' in the associated club column - so we need a way to export individual files per club with the names of those interested. We typically have 1000 rows, and we have over 200 clubs (columns) so we couldn't do this manually.

In 2015 when we started this process, we solved this at SuperUser (here) with the following code:

Sub FilterData()
    Dim Responses As Worksheet
    Dim Column As Long
    
    Set Responses = ThisWorkbook.Worksheets("Export")
    Column = 5
    
    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, Column).Delete Shift:=xlUp
                .Columns(2).Resize(, .Columns.Count - 1).Delete Shift:=xlShiftToLeft
            End With
            .Close SaveChanges:=True, FileName:="/Users/myname/Desktop/Data/" & Responses.Cells(1, Column).Value & ".xlsx”"
        End With
    Column = Column + 1
    Loop
End Sub

That worked because our set-up only needed to copy one column over - email.

Now we have more columns to save to the individual files.

This is the new layout (compared to the one from 2015)
enter image description here

Everything I've modified/added/removed has broken the script. I suspect it's a modification to .column resize section:

.Columns(2).Resize(, .Columns.Count - 1).Delete Shift:=xlShiftToLeft

My initial thought was that I could change .Columns(2) to (5) - but that breaks the script.

How can I modify this to copy the range of the first four columns as opposed to the first column?

Upvotes: 1

Views: 174

Answers (1)

Christofer Weber
Christofer Weber

Reputation: 1474

Indeed very close. Changing .Columns(2) to (5) is fine, as long as you compensate at the end. Try:

.Columns(5).Resize(, .Columns.Count - 4).Delete Shift:=xlShiftToLeft

The reason for this, is that Columns.Count will return ALL columns in the document.
So by moving the target column, and resizing the target with the number of ALL columns, we end up "outside of the document". Hence the error.

Upvotes: 2

Related Questions