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 |
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 |
All times are GMT +1. The time now is 06:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com