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.
Kind regards,
Leela.