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

RuiFlora

Board Regular
Joined
Feb 28, 2014
Messages
58
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,218
Can you explain this in detail:
The ranges have more than one column but I just want to copy the data from one column
I can't see that you are attempting that at all. Or am I misunderstanding something?

Also, are you just wanting the values taken across, or formatting also? ( do you really need to copy and paste, or will setting cells in sheet Test to the values in the source range be sufficient? )
 

RuiFlora

Board Regular
Joined
Feb 28, 2014
Messages
58
Can you explain this in detail:


I can't see that you are attempting that at all. Or am I misunderstanding something?

Also, are you just wanting the values taken across, or formatting also? ( do you really need to copy and paste, or will setting cells in sheet Test to the values in the source range be sufficient? )

I just want to take the values. Not formatting them. i give you an example of data to better understanding.

ABC
1101510
22
3211151
4516030
530318
6
7888

<tbody>
</tbody>

In that case, my range is for example: =CAR!A1:B1,A3:B5,A8:B8. What I pretend is to copy just the column 1 of the (non contiguous) range that I refer, to another place, continuously, like I put in column C.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,218
Are you just skipping blanks in the first column, or is that just your example?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,218
Try replacing:
Code:
With myRange
.Column(1).Select
End With

Selection.Copy
Sheets("Test").Select
Range("D11").Select
ActiveSheet.Paste
with:
Code:
Set targetcell = Worksheets("Test").Range("D11")
For Each carblock In myRange.Areas
    targetcell.Resize(carblock.Rows.Count).Value = carblock.Resize(, 1).Value
    Set targetcell = targetcell.Offset(carblock.Rows.Count)
Next
 

RuiFlora

Board Regular
Joined
Feb 28, 2014
Messages
58
Try replacing:
Code:
With myRange
.Column(1).Select
End With

Selection.Copy
Sheets("Test").Select
Range("D11").Select
ActiveSheet.Paste
with:
Code:
Set targetcell = Worksheets("Test").Range("D11")
For Each carblock In myRange.Areas
    targetcell.Resize(carblock.Rows.Count).Value = carblock.Resize(, 1).Value
    Set targetcell = targetcell.Offset(carblock.Rows.Count)
Next

Thanks a lot! it is working! However, is it possible to copy and paste, instead of to Range("D11"), to be at the first empty column to the right? Each time i execute the code, it will copy to the next empty column. It would be great!
 

RuiFlora

Board Regular
Joined
Feb 28, 2014
Messages
58
I adapted the code you sent me. It is copying but not the way I pretend. I have two combobox, and in the second one I select a value, which is related to a column. That is where I "select" the column that I want to copy to "Test". The problem is that in the way the code is built, it is copying only the first column, and not selecting, depending on what I select in the second combobox. i dont know if the problem is with the .Resize command. I hope my explanation is understandable.



Private Sub CommandButton2_Click()


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


Dim lColumn As Integer
Dim Deliver As String


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


'First selection
If ComboBox1.Value = "BMW" Then

'get the value of second selection
Deliver = ComboBox2.Value

'get the column com second selection
colNum = WorksheetFunction.Match(Deliver, ActiveWorkbook.Sheets("BMW").Range("D3:S3"), 0)

'get the colum 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 If


End Sub
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,218
Sorry, but I can't follow this:
... it is copying only the first column, and not selecting, depending on what I select in the second combobox...
 

RuiFlora

Board Regular
Joined
Feb 28, 2014
Messages
58
Sorry, but I can't follow this:
Sorry to be not clear in the explanation.
What meant was that:
The code I posted above is for a button in a userForm. In this userForm I have a combobox to choose what i want to copy from another sheet, per column (in this case models of BMW). Those models are in specific columns (ex: A, B, C, etc), and this is what i want to copy.

The code you posted is copying data, but only for column A of the range. I want to be able that, depending on the model i choose from the combobox, copying the corresponding column.
 

Forum statistics

Threads
1,082,126
Messages
5,363,321
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top