CODEWITHSUNDEEP
vbaexcelexcel-2010
Petay87
Petay87

Reputation: 1773

Excel VBA - How do I select multiple entire columns from defined ranges?

What I am trying to do:

I am trying to copy only certain columns from a spreadsheet based on the header. Rather than do loops to copy each column individually, I am trying to copy a number of columns at once.

What I have so far:

With wb.Worksheets("Sheet1")
    Set lasthead1 = .Range("1:1").Find(What:="*", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    Set headrng1 = .Range("A1", lasthead1)
    For Each c In headrng1
        If Left(c, 1) = "-" Then c = Mid(c, 2, Len(c) - 1)
        If Left(c, 1) = "+" Then c = Mid(c, 2, Len(c) - 1)
    Next c
    Set PRIhead = headrng1.Find(What:="Priority", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    Set LOGhead = headrng1.Find(What:="Log Date", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    Set TYPEhead = headrng1.Find(What:="Type", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    Set CALLhead = headrng1.Find(What:="Call Status", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    Set DEShead = headrng1.Find(What:="Description", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    Set IPKhead = headrng1.Find(What:="IPK Status", LookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    .Range(PRIhead, LOGhead, TYPEhead, CALLhead, DEShead, IPKhead).EntireColumn.Copy
End With

The issue:

Appears to be with the following line of code:

.Range(PRIhead, LOGhead, TYPEhead, CALLhead, DEShead, IPKhead).EntireColumn.Copy

My Sources:

I used the accepted answer on This Question to put my line of code together.

Upvotes: 2

Views: 421

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

Follow up from comments.

The reason of the issue is because Range(cell1,[cell2]) object can accept only 2 cells as parameters.

So you need to use

wb.Application.Union(PRIhead, LOGhead, TYPEhead, CALLhead, DEShead, IPKhead).EntireColumn.Copy

I used wb.Application.Union because (as follow up from comments), workbook wb is a part of another application object.

Upvotes: 2

Related Questions