Application.Union VBA in specified order

Mojo689

New Member
Joined
Feb 18, 2016
Messages
10
Office Version
2016
Platform
Windows
I have this vba code that is taking specific columns from a worksheet, and copying and pasting them into another worksheet. It works for other worksheets I'm copying because the columns being copied are in alphabetical order; however, for this specific worksheet, I want it in the order specified (seen below). I know that in order to do this, I just need to create an array of the column order. My problem is, I'm not sure how to include that array into my code, or how that specific line of copying code would change. Any suggestions that would help would be appreciated :)

'episign table for COPY job

Dim esrtbl As ListObject, essize As Long, lastES As Long
Set estbl = Worksheets("EpiSign").ListObjects("TES")
essize = estbl.ListRows.Count
lastES = estbl.HeaderRowRange.Offset(essize + 1).End(xlUp).Row

'episign table for PASTE job

Dim EStbl2 As ListObject, tblGES As Long, EStbl2size, tblESsize As Integer

Set EStbl2 = Worksheets("Gantt").ListObjects("SES")
tblGES = EStbl2.ListRows.Count
EStbl2size = essize - tblGES
tblESsize = tblGES - essize

With EStbl2
If tblGES < essize Then
For counter = 1 To EStbl2size
EStbl2.ListRows.Add , alwaysInsert:=True
Next counter
End If

If essize < tblGES Then
EStbl2.ListRows(2).Range.Resize(tblESsize).Delete (xlShiftUp)
End If

End With

With Worksheets("EpiSign")

Application.Union(.Range("B2:B" & lastES), .Range("P2:P" & lastES), .Range("H2:H" & lastES), .Range("L2:L" & lastES), .Range("R2:R" & lastES)).Copy
EStbl2.HeaderRowRange.Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

End With


Thanks!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Mojo689

New Member
Joined
Feb 18, 2016
Messages
10
Office Version
2016
Platform
Windows
I got tired of trying to make an array work, so I solved this by just moving the one column within the original table so that my code works (the "P" column moved up to follow the B column - making the new column ranges B, G, I, M, R, which works as it's alphabetical.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,101
Messages
5,509,244
Members
408,718
Latest member
Bea2136

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top