Macro to copy nonadjacent range and paste
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")
ML wrote:
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
|