VBA Copy cells from non-contiguous ranges to paste on another sheet

[RuiFlora]

Hello, I am trying to build a code that copy cells from non-contiguous ranges and paste the data on another sheet where the cells are contiguous. The ranges have more than one column but I just want to copy the data from one column (for example the first column). This is the code I have for now, but it gives me an error:

Sub CarCopy_Click()


Dim BrandCopy As Range
Set BrandCopy = Worksheets("Cars").Range("BrandCopy")
Dim ModelCopy As Range
Set ModelCopy = Worksheets("Cars").Range("ModelCopy")


Dim myRange As Range
With Sheet13
Set myRange = Union(.Range("BrandCopy"), .Range("ModelCopy"))
End With

With myRange
.Column(1).Select
End With

Selection.Copy
Sheets("Test").Select
Range("D11").Select
ActiveSheet.Paste


End Sub

Or, is there any way to copy the cells from a unique non-contiguous range? (ex: =Cars!$D$5:$AB$9,PC!$D$15:$AB$20) and paste them continuously in another sheet?

Hope someone can help me, i am really stuck with that
Many thanks in advance

 

[GlennUK]

OK , I understand now.

Change this:

Resize(, 1).Value

to be

Offset(0,number_of_columns_to_offset).Resize(, 1).Value

... where you calculate number_of_columns_to_offset depending on your required column ( offset by 0 to get the first column ).

Does that work?

 

[RuiFlora]

OK , I understand now.

Change this:

Resize(, 1).Value

to be

Offset(0,number_of_columns_to_offset).Resize(, 1).Value

... where you calculate number_of_columns_to_offset depending on your required column ( offset by 0 to get the first column ).

Does that work?

Like that?
For Each copy In HistCopy.Areas
targetcell.Resize(copy.Rows.Count).Value = copy.Offset(0, colNum).Resize(, 1).Value
Set targetcell = targetcell.Offset(copy.Rows.Count)
Next.

It didn't work. it is still copying the first and it isn't copying to the first empty in "test"

 

[GlennUK]

Show your full code. Explain exactly how it's supposed to work. I can see bits, and can see how it might be going wrong, but it's not easy to debug when you can't see it all.

 

[RuiFlora]

Show your full code. Explain exactly how it's supposed to work. I can see bits, and can see how it might be going wrong, but it's not easy to debug when you can't see it all.

The full code of the button is the following:

Private Sub CommandButton2_Click()

Dim wsTest As Worksheet
Set wsTest = Worksheets("Test")

Dim lColumn As Integer
Dim Model As String

Dim copy As Range
Dim HistCopy As Range
Set HistCopy = Worksheets("BMW").Range("HistCopy")

'Get the value of the model (in BMW sheet) i want to copy
Model = ComboBox2.Value

'Search for the column of the model selected in "BMW" sheet
colNum = WorksheetFunction.Match(Deliver, ActiveWorkbook.Sheets("BMW").Range("D3:S3"), 0)

'get the colum number of the the first empty cell in "Test"
lColumn = wsTest.Range("C14:CC14").Cells.Find(What:="", SearchOrder:=xlColumns, _
SearchDirection:=xlNext, LookIn:=xlValues).Column - 3

'set the destination in "Test" sheet
Set targetcell = Worksheets("Test").Cells(11, lColumn + 3)

'Copy data from all areas in HistCopy range and paste in "Test" sheet
For Each copy In HistCopy.Areas
targetcell.Resize(copy.Rows.Count).Value = copy.Resize(, 1).Value
Set targetcell = targetcell.Offset(copy.Rows.Count)
Next

End Sub

I put in comment, what i am trying to do with each line.
Thanks

 

[GlennUK]

What is Deliver in:

'Search for the column of the model selected in "BMW" sheet
colNum = WorksheetFunction.Match(Deliver, ActiveWorkbook.Sheets("BMW").Range("D3:S3"), 0)

... where does that get set?

 

[RuiFlora]

What is Deliver in:

'Search for the column of the model selected in "BMW" sheet
colNum = WorksheetFunction.Match(Deliver, ActiveWorkbook.Sheets("BMW").Range("D3:S3"), 0)

... where does that get set?

Deliver is the Model. It is what i choose in combobox2. (Serie 1, Serie 3, Series 5, etc).
The colNum gets the column number my choice in BMW sheet.

 

[GlennUK]

Deliver is the Model. It is what i choose in combobox2. (Serie 1, Serie 3, Series 5, etc).
The colNum gets the column number my choice in BMW sheet.

No it isn't ... from what I'm reading you have set a variable of Model as being the Combobox2 value. Or am I mistaken???

 

[RuiFlora]

No it isn't ... from what I'm reading you have set a variable of Model as being the Combobox2 value. Or am I mistaken???

You're right. My bad. Deliver is wrong, it is Model instead

 

[GlennUK]

So, did you change it and try again? If you've changed your code, please re-post it.

 

[shpartan]

hello good people.I am new here learning VBA. My challenge is closely related to this one.

I have 2 non contiguous ranges in the same sheet.
I need a way of copying the resultant Union range and pasting it in a different sheet in the same workbook.

Set unionrange = Union(Rnga, Rngb)