Macro to copy nonadjacent range and paste

[ML]

The macro aims to select whatever in column A and whatever in column C, then
copy and paste to a new workbook as column A and B. Since the number of rows
in column A and C are uncertain, I have to use End Down to select the cells.
However, the marco below only copy and paste column A but not C. What's
wrong?

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:A5,C1").Select
Range("C1").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste

 

[Dave Peterson]

Since your pasting to a new worksheet in a new workbook, couldn't you just
bypass the last cell stuff and just copy|paste the entire column.

Dim DestWks as worksheet
dim ActWks as worksheet

set actwks = activesheet

'add a new single sheet workbook and use that sheet
set destwks = workbooks.add(1).worksheets(1)

actwks.range("A1").entirecolumn.copy _
destination:=destwks.range("A1")

actwks.range("C1").entirecolumn.copy _
destination:=destwks.range("b1")


=======
But if you wanted, you could do something like:

Dim DestWks as worksheet
dim ActWks as worksheet
dim RngToCopy as range

set actwks = activesheet

'add a new single sheet workbook and use that sheet
set destwks = workbooks.add(1).worksheets(1)

with actwks
set rngtocopy = .range("a1",.cells(.rows.count,"A").end(xlup)
end with

rngtocopy.copy _
destination:=destwks.range("A1")

with actwks
set rngtocopy = .range("c1",.cells(.rows.count,"c").end(xlup)
end with

rngtocopy.copy _
destination:=destwks.range("b1")