Excel specific column extraction

Question

I've been trying to extract certain columns from an Excel sheet from a multitude of unwanted columns. I've been running the following script:

Public Sub extractCol()
  Set range1 = Range("A:A, BI:BI, C:C, L:L")
  range1.Copy
  Set newbook = Workbooks.Add
  ActiveCell.PasteSpecial Paste:=xlPasteValues
End Sub

This formula extracts the data however, the output comes out as:

Column A   Column C   Column L   Column BI

When the output I need is:

Column A   Column BI   Column C   Column L

How can I change the code to get the desired output?

A [Union](https://msdn.microsoft.com/en-us/library/office/ff834621.aspx) of cells will homogenize itself into a left-to-right / top-to-bottom arrangement. You need multiple operations if you wish to restructure the target. — , Sep 07 '16 at 15:12
@Sage Mohan you can try my code (below), it's not ideal, but it will get the job done for you case — Shai Rado, Sep 08 '16 at 04:46

score 0 · Accepted Answer · answered Sep 07 '16 at 18:24

It's quick (and dirty), but the following code will get you what you want:

Public Sub extractCol()

Dim newbook                 As Workbook
Dim ShtSource               As Worksheet
Dim ShtDest                 As Worksheet

' modify Sheet 1 to your Source sheet name
Set ShtSource = ThisWorkbook.Sheets("Sheet6")

Set newbook = Workbooks.Add

' modify Sheet 1 to your Destination sheet name
Set ShtDest = newbook.Sheets("Sheet1")

ShtSource.Columns("A:A").Copy Destination:=ShtDest.Columns(1)
ShtSource.Columns("BI:BI").Copy Destination:=ShtDest.Columns(2)
ShtSource.Columns("C:C").Copy Destination:=ShtDest.Columns(3)
ShtSource.Columns("L:L").Copy Destination:=ShtDest.Columns(4)

End Sub

Excel specific column extraction

1 Answers1