Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy nonadjacent range and paste
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy, Paste and Rename a Range using a macro | Excel Discussion (Misc queries) | |||
Macro - copy a range and paste it in a new sheet 12 times | Excel Worksheet Functions | |||
macro to copy/paste range for printing | Excel Programming | |||
Macro to copy, paste in a range and then delete | Excel Discussion (Misc queries) | |||
Macro to copy range of cells and paste into 1 sheet | Excel Programming |