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!
'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!