I have a somewhat sticky problem I'm trying to sort out and I'm hoping you would be able to help me out once again.
The workbook I'm currently working on has one destination worksheet named "Master". There are currently five source sheets which, so I've been told, may increase to ten source sheets. The source sheet names are Landing1, Landing2------->Landing5.
Each source sheet has headers in Row1 with data commencing in Row2. Rows used will vary in all worksheets.There are six columns (A:F).
The Master sheet has headers in Row3, columns A:F as well. Row1 has some "sticky" notes in it for User instructions and Row2 is empty. Column G is empty. In cell H3, there is a data validation drop down list with all the source sheet names.
I was hoping to make a number of worksheet selections at once from the drop down list and then have all the data from the selected worksheets copy/pasted to the Master sheet from cell A4 on. Hence, if I were to select Landing1 and Landing5 from the drop down then the data from these two worksheets would be copy/pasted to the Master worksheet. If I only selected, say, the Landing3 worksheet from the drop down then only the data from this single worksheet would be copy/pasted to the Master worksheet.
Clearing the existing data in the Master worksheet prior to the following copy/paste would be ideal if possible.
With the macro that I currently have operating, I can only select one source sheet at a time. I was hoping that there is a way to select any number of required source worksheets at a time from the drop down and copy/paste the data from the multiple selections. I found the following code from a Trump Excel tutorial which allows a User to select multiple worksheets from a drop down but it doesn't work or, rather, nothing happens (no errors or warnings). I have pasted it into the Master worksheet module.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com ' To allow multiple selections in a Drop Down List in Excel (without repetition) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Target.Address = "$C$2" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else If InStr(1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & ", " & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Here's hoping and thank you for at least reading this post.