#1
|
|||
|
|||
Copy and Paste certain columns from selected rows.
Hello, From “Sheet1” I want to be able to select random rows and when the macro is run columns B, C, E, G and J from the selected rows will be pasted into the “Paste” sheet. I found this code in a forum that seems to work except for they wanted Rows A, B and E.
Sub Macro1() Dim rng As Range For Each rng In Selection.Areas Union(rng.Resize(, 2), rng.Resize(, 1).Offset(, 4)).Copy Sheets("Paste").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Next rng Worksheets("Paste").Activate End Sub I believe I understand the “Copy Sheets("Paste").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)” part and it works just as I need. I do not understand how “ Union(rng.Resize(, 2), rng.Resize(, 1).Offset(, 4)).” selects columns A,B and E and how to change it to get me to the results I need. Thanks Note: Union(rng.Resize(, 2), rng.Resize(, 1).Offset(, 4)).Copy Sheets("Paste").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Is one line. |
#2
|
||||
|
||||
This should make it a bit easier to choose which columns you want copying:
Code:
Sub blah() Dim rng As Range For Each rng In Selection.Areas With rng.EntireRow Union(.Columns("B:C"), .Columns("E:E"), .Columns("G:G"), .Columns("J:J")).Copy Sheets("Paste").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) End With Next rng Worksheets("Paste").Activate End Sub With rng.EntireRow to: With rng Note that whichever you choose above, you won't need to select ranges which cover all the columns you want to copy, only the leftmost column. Moreover, if you leave EntireRow in, it doesn't matter which column(s) you choose at all. |
#3
|
|||
|
|||
Thanks for the help here. Your code seems ideal for my application. The EntireRow tip was also very helpful. Thanks also for the explanation on the original code.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy unique worksheet to another workbook refuses says columns rows differ | DBenz | Excel | 3 | 02-16-2023 07:45 AM |
Copy selected text from one document and paste to new document in same position on the page | gasparik | Word VBA | 1 | 05-11-2020 05:41 AM |
How do I copy text from wxcel and paste to word , eliminating lines but keeping columns aligned? | KPS2019 | Word | 4 | 05-31-2019 04:04 PM |
how can i split a column of rows in half without copy and paste.. | Lindseyfparker | Excel | 2 | 04-25-2014 12:52 AM |
Find specific rows then copy and paste to new doc | konopca | Word VBA | 5 | 02-20-2014 02:34 PM |