VBA Copy From Multiple No Sequential Columns

[ir121973]

Hi,

I wonder whether someone may be able to help me please.

From searching on the web I've found the script below which copies a range of columns from a specific row, until the last, creates a new workbook and copies the data into the new workbook.

Dim lRow, lCol As Integer


Sheets("Amalgamation of Search").Select
lRow = Range("A" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column


For Each cell In Range(Cells(, "D"), Cells(1, lCol))
    Union(Range("A1:A" & lRow), Range(Cells(1, cell.Column), Cells(lRow, cell.Column))).Copy
    Workbooks.Add
    Range("A1").PasteSpecial
    ActiveWorkbook.SaveAs Filename:="\\c\s\CAF1\DD\DDCOPS\SC Data.xls"
Next cell


Application.CutCopyMode = False
End Sub

The code works, but there are just some tweaks that I'd like to make, but I admit I'm a little rusty on VB.

Rather than just copying the columns A and D I'd like to copy columns A, C and D.

I just wonder whether someone may be able to look at this please, and offer some guidance on how I can change.

Many thanks and kind regards

Chris

 

[Sunny Kow]

You could consider deleting column B after copying and before saving.

 

[ir121973]

Hi, thank you for this, but at the moment it's not copying column B, doesn't really provide me with the solution I need.

Many thanks and kind regards

Chris

 

[ir121973]

Hi All,

I manged to solve this with the following solution:

Dim lRow, lCol As Integer


Sheets("Amalgamation of Search").Select
lRow = Range("A" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column


For Each cell In Range(Cells(, "D"), Cells(1, lCol))
    Union(Range("A1:A" & lRow), Range("C1:C" & lRow), Range(Cells(1, cell.Column), Cells(lRow, cell.Column))).Copy
    Workbooks.Add
    Range("A1").PasteSpecial
    ActiveWorkbook.SaveAs Filename:= _
    "\\c\s\CAF1\Digital Delivery Group\DD\Data Security\SC Data.xls"
    ActiveWorkbook.Close
Next cell


Application.CutCopyMode = False
End Sub

Many thanks and kind regards

Chris