Hello!
My ultimate goal is to pull 3 types of information from n number of worksheets into a summary page. I was successful with copying and pasting unique values (see below), however, I am having issues copying one cell from each worksheet and having it repeat multiple times.
I have multiple worksheets that look something like this:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Amanda[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Color[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gold[/TD]
[/TR]
</tbody>[/TABLE]
My code for copying ID and Color is:
Without having to count the number of entries myself, how do I now copy "Amanda" for each entry for Amanda (in this case 3 times)? Thanks!
My ultimate goal is to pull 3 types of information from n number of worksheets into a summary page. I was successful with copying and pasting unique values (see below), however, I am having issues copying one cell from each worksheet and having it repeat multiple times.
I have multiple worksheets that look something like this:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Amanda[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Color[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gold[/TD]
[/TR]
</tbody>[/TABLE]
My code for copying ID and Color is:
Code:
Private Sub CommandButton1_Click()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If Not wks.Name = "Summary" Then
wks.Range("A7:A" & wks.Cells(Rows.Count, "A").End(xlUp).Row).Copy _
Destination:=Worksheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1)
wks.Range("D7:D" & wks.Cells(Rows.Count, "D").End(xlUp).Row).Copy _
Destination:=Worksheets("Summary").Cells(Rows.Count, "B").End(xlUp).Offset(1)
End If
Next
End Sub
Without having to count the number of entries myself, how do I now copy "Amanda" for each entry for Amanda (in this case 3 times)? Thanks!