Copying non-adjacent columns to other sheet

Maraua

New Member
Joined
Mar 28, 2019
Messages
4
Hi,
I have the following code that copies filtered data from one sheet to another. I use the copied data to populate a listbox on a userform:
Code:
    With ws        .AutoFilterMode = False
        
        Set rng = ws.Range("B8:R8" & .Cells(Rows.Count, "B").End(xlUp).row)
        rng.Copy ws1.Range("B7")
        rngdest = "Sheet2!" & .Range("B8").CurrentRegion.Address
    End With
    With ListBox1
        .RowSource = rngdest

        .ColumnCount = 17
        .ColumnWidths = "0;60;90;0;24;40;20;130;24;42;42;55;20;120;130;110;30"
    End With
    Set rng = Nothing
I have been trying to adjust this to copy multiple, non-adjacent columns.
When I use
Code:
 Set rng = ws.Range("B8:R" & ", T8:Z" & .Cells(Rows.Count, "B").End(xlUp).row)I get a runtime error 1004.

Any thoughts on how to accomplish this?
Thanks.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi & welcome to MrExcel
Try
Code:
Set Rng = Ws.Range("B8:R" & .Cells(Rows.Count, "B").End(xlUp).Row & ", T8:Z" & .Cells(Rows.Count, "B").End(xlUp).Row)
 
Upvote 0
Try this

Code:
dim lr as long
lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = ws.Range("B8:R" & lr & ",T8:Z" & lr)
 
Upvote 0
Thanks guys,
Both work great.
I prefer Dante's. I will have 15-20 ranges to copy.
So I will end up with less code, and when I tried both the codes.

I had the impression that Dante's was a bit faster. Or is this just my imagination?
 
Upvote 0
I wouldn't expect any noticeable difference in time, although Dante's code only calculates the last row once, rather than twice
 
Upvote 0
Not particularly, although I would tend to go with Dante's code as it's more compact & if you need to copy other ranges you can just keep using "lr", rather than recaculating it.
 
Upvote 0
Sorry, I was eating.


Nor do I see differences, neither in time nor in advantages, pro's: it could be that the code is more compact and less risk of making mistakes when using it several times
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top