Thread: [SOLVED] Select Multiple Rows But Only Copy Specified Columns

This code will let you select the rows to copy, but will only copy the columns(A:BQ) in your selection. So Select the rows to copy then run the macro, it will then paste to A1 in Sheet2 .

You can select many rows at once, but unfortunately I could not get it to work if you select different rows at once, ie: if Rows 1,3,6,9 are selected. It only works if the rows are together.

Hopefully it will work for your Excel Version.

Code:

Sub CopyRows()

    Dim ws As Worksheet, r As Range

    Set ws = Worksheets("Sheet2")
    Set r = ws.Range("A1")

    Selection.Columns("A:BQ").Copy Destination:=r

End Sub

hi,

The below modified version of Dave's code (or perhaps the code in the attached file) may work for you...

Code:

Option Explicit
Sub Modified_CopyRows()
Dim ws As Worksheet
Dim FirstBlankRw As Range
Dim AreaInSlctn As Range
Dim Slctn As Range
application.screenupdating = false
    Set ws = Worksheets("Sheet2")
    Set Slctn = Selection
    For Each AreaInSlctn In Slctn.Areas
        With ws
            Set FirstBlankRw = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With
        AreaInSlctn.Columns("A:BQ").Copy Destination:=FirstBlankRw
    Next AreaInSlctn
'release memory
set Slctn = Nothing
set FirstBlankRw =Nothing
set ws = nothing
application.screenupdating = true
End Sub

Dave,
I learnt this approach of identifying the separate areas from a file of John Walkenbach's called "copymult.xls" which I downloaded a few years ago from his site (? I can't see it on his "new" site layout) & have attached this for interest.
Note: Charles William's site discusses an error in the way Excel handles multiple area ranges when processing UDF's - not relevant for the OP in this case, but it could possibly become an issue if someone tried to modify the above concept for use in a UDF.

Others, if using John's code or the attached file, please identify the source.

hth
Rob

hi,

Hmmm,
Not as it's written. The code in my post (not the file) goes through each "contiguous area" (AreaInSlctn) one at a time & pastes each one, so if you were to remove the "Destination:=" argument & then try to have it paste to another file later, it is likely to only copy the last AreaInSlctn into your selected file. The reasons I prefer to use the "Destination:=" argument is that it:
- completes the action in one go, which
- minimises errors of the Clipboard being cleared before a Paste occurring (eg by pressing [Esc] or changing the Clipboard when working on another application, such as email, while the macro runs etc) &
- prevents the need for

Code:

application.cutcopymode = false

Is there a standard naming convention for the file names?
If so, we could incorporate your other code & use logic to paste into the correct file.
Can you please expand on the facts to give an overall explanation of what your collection of macros do?

If you really want to keep the two actions of Copying & Pasting separate, we need to maintain/make an array of all the individual areas in your selection which is likely to be easy based on the code in John W's file - but it may take me a while to wrap my head around it...

Let us know which approach you'd prefer

Rob

hi Amy Kate,

Thanks for the feedback & also for adding to my reputation

If you are happy with the solution can you please mark the thread as Solved?
(see Forum Rules for instructions)

Rob