Copy existing dynamic ranges into one list

[AusSteelMan]

Hi everyone.

Hoping for some help in copying a few dynamic ranges into another sheet as one list.

For example, I have a sheet named ThemeValidationList which contains 5 dynamic ranges (named Theme1 to Theme5).

I would like to know if there is a method that would copy each of these ranges and paste them into a new "continuous" list (on the same or a different sheet is fine).

Since the lists are dynamic, can this new list update automatically (thinking a change worksheet VBA perhaps).

I have mocked up a visual of what I am thinking:

Excel Workbook
	A	B	C	D	E	F
1	Total List	Theme1	Theme2	Theme3	Theme4	Theme5
2	apple	apple	cola	truck	dog	pen
3	banana	banana	lemonade	car	cat	pencil
4	orange	orange	*	bike	horse	highlighter
5	cola	*	*	plane	pig	rule
6	lemonade	*	*	*	goat	notepad
7	truck	*	*	*	*	battery
8	car	*	*	*	*	*
9	bike	*	*	*	*	*
10	plane	*	*	*	*	*
11	dog	*	*	*	*	*
12	cat	*	*	*	*	*
13	horse	*	*	*	*	*
14	pig	*	*	*	*	*
15	goat	*	*	*	*	*
16	pen	*	*	*	*	*
17	pencil	*	*	*	*	*
18	highlighter	*	*	*	*	*
19	rule	*	*	*	*	*
20	notepad	*	*	*	*	*
21	battery	*	*	*	*	*
Sheet2

So I am after Column A being generated automatically from the items in Cols B to F - which are dynamic ranges.

I'll also add that duplicates in the output list are acceptable

Thanks for your consideration of this question and extra thanks for any help or suggestions you can provide.

Best Regards,
Darren

 

[JLGWhiz]

This will consolidate your lists into column A. Copy the procedure below into your standard code module1. You can then eithe run it manually or assign it to a button and click the button to run it. To have it automatically update would require a worksheet event procedure.

Code:
Sub dynRng()
Dim sh As Worksheet, c As Range
Set sh = Sheets(1) 'Edit sheet name
Columns(1).Insert
For Each c In sh.Range("B2:F2")
lr = sh.Cells(Rows.Count, c.Column).End(xlUp).Row
If sh.Range("A2") = "" Then
With sh
.Range(.Cells(2, c.Column), .Cells(lr, c.Column)).Copy .Range("A2")
End With
Else
With sh
.Range(.Cells(2, c.Column), .Cells(lr, c.Column)).Copy .Cells(Rows.Count, 1).End(xlUp)(2)
End With
End If
Next
End Sub
Code:

 

[JLGWhiz]

Here is the procedure to automatically update the list in Column A as items are added to the other lists. It will not automatically delete items from the consolidated list in column A. To use this procedure, copy it into the sheet code module for the sheet containing the lists. To access the code module, right click the sheet name tab at the bottom of the screen and then click "View Code" in the pop up menu. That sheet code module should then open so that you can paste the procedure into it. Do NOT install this procedure until you have created your consolidated list, or you will generate undesirable results. Once this procedure is installed, any changes to the sheet will cause the macro to run. However, if the changes are not to data in columns B:F range plus one row, the change will be ignored by the macro.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = ActiveSheet
lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
Set rng = sh.Range("B2:F" & lr + 1)
If Not Intersect(Target, rng) Is Nothing Then
sh.Cells(Rows.Count, 1).End(xlUp)(2) = Target.Value
End If
End Sub
Code:

 

[AusSteelMan]

Thanks very much JLGWhiz.
Both did exactly as you described.

Hopefully I can now tweak it to suit my actual application.

Many thanks.
Darren