Copy & Paste from multiple drop down selection.

[Leelabird]

Good morning everyone,

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

Is this possible or should I just forget this and continue on with a single selection at a time?

Here's hoping and thank you for at least reading this post.

Kind regards,
Leela.

[HansV]

What happens if you change

If Target.Address = "$C$2" Then

to

If Target.Address = "$H$3" Then

since H3 is your data validation cell?

[vcoolio]

Hello Leela,

Interesting! That's a very precise description. Thanks!

In the code that you have supplied, I would suspect that the target address has caused the lack of response from the code. The target address (or Target.Address) in the code is C2. Based on your description, the actual target address is H3. Change this and see what happens.

Anyway, I believe that this can be sorted out for you. However, when multiple selections are made from a drop down, only one selection can be made at a time. So, if you need three sheets selected, you'll need to select each one separately and you'll see them listed in the drop down together, usually horizontally ( as the code you supplied indicates).

It's all a bit involved but let's try this:-

In the Master sheet module, leave the Worksheet_Change code where it is. BTW, thanks to Sumit Bansal for the code......I won't have to write something similar now!
In a standard module, place this code and assign it to a button:-

Code: Select all

Sub LeelaTest()

        Dim i As Long
        Dim Rng As Range, c As Range, ar As Variant
        Dim ws As Worksheet, sht As Worksheet, wks As Worksheet
        
Application.ScreenUpdating = False
        
        Sheet6.Columns(1).Clear
        Sheet1.[H3].Copy
        Sheet6.[A1].PasteSpecial xlValues
        
        Set Rng = Sheet6.[A1]
                Rng.TextToColumns Destination:=Rng, DataType:=xlDelimited, _
                ConsecutiveDelimiter:=True, Tab:=True, _
                Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
                Sheet6.[A1:E1].Copy
                Sheet6.[A2].PasteSpecial , Transpose:=True
        For Each c In Sheet6.Range("A2", Sheet6.Range("A" & Sheet6.Rows.Count).End(xlUp))
              c.Value = Trim(c)
        Next c
                Sheet6.[A1:E1].Clear
                Sheet6.[A1] = "Sheet List"
                ar = Sheet6.Range("A2", Sheet6.Range("A" & Sheet6.Rows.Count).End(xlUp))
            
                Sheet1.[A3].CurrentRegion.Offset(1).Clear
               
        On Error Resume Next
        
        If Sheet1.[H3].Value = "All" Then
                For Each wks In Worksheets
                        If wks.Name <> "Master" And wks.Name <> "List" Then
                             wks.UsedRange.Offset(1).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                        End If
                Next wks
        End If
        
        If IsArray(ar) Then
                For i = 1 To UBound(ar)
                     Set ws = Sheets(ar(i, 1))
                     ws.UsedRange.Offset(1).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                Next i
        Else
                Set sht = Sheets(ar)
                sht.UsedRange.Offset(1).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
        End If
        
        Sheet1.[H3].ClearContents
        
Application.ScreenUpdating = True

End Sub

Firstly, you'll need to create a list of worksheets to be placed in the data validation drop down:-

- Select a column well away from the data set, say Column Q.
- In Q1, place the heading Sheet List.
- In Q2, type in "All" (without the quotation marks of course).
- In Q3:Q7, Type in the sheet names.
- Select Q1:Q7, go to the Formulas tab in the ribbon and in the Defined Names group, click on the Define Name down arrow, select Define name.
- The New Name dialogue box should appear with "Sheet List" in the name box. Click OK. You now have a named range to use.
- High-light (select) Q1:Q7 again and go to the Insert tab in the ribbon.
- Select Table in the Tables group. The Create Table dialogue box will appear. You should see the selected range appear in the "Where is the data for your table" box.
- Tick the "My table has headers" box. Click OK.

You now have an expandable table for your worksheets so you'll be able to add/remove worksheet names as the need arises. These worksheet names will appear in the data validation drop down (next step).

Data Validation (you're probably already familiar with this but........):
- Select cell H3.
- Go to the Data Tab in the ribbon and click on the Data Validation down arrow in the Tools group. Select Data Validation.
- The Data Validation dialogue box will open.
- In the Allow box, select List.
- With your mouse, select Q1:Q7. You will see "=Sheet List" appear in the Source box (you can just type in =Sheet List if you like). Click OK.
You will now see the list appear in H3 as your drop down selections. Each time that you add a new sheet name in the Sheet List table, it will be immediately added to the drop down in H3.
You will now be able to add multiple sheet names to the H3 validation cell (one sheet, three sheets, five sheets etc.), one at a time though.

Next step:-

- Open a new sheet and name it "List". This will be used as a helper sheet.
- In the List sheet, this part of the code will work it's majic:-

Code: Select all

     Set Rng = Sheet6.[A1]
                Rng.TextToColumns Destination:=Rng, DataType:=xlDelimited, _
                ConsecutiveDelimiter:=True, Tab:=True, _
                Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
                Sheet6.[A1:E1].Copy
                Sheet6.[A2].PasteSpecial , Transpose:=True
        For Each c In Sheet6.Range("A2", Sheet6.Range("A" & Sheet6.Rows.Count).End(xlUp))
              c.Value = Trim(c)
        Next c
                Sheet6.[A1:E1].Clear
                Sheet6.[A1] = "Sheet List"
                ar = Sheet6.Range("A2", Sheet6.Range("A" & Sheet6.Rows.Count).End(xlUp))

As the H3 drop down will have the worksheets listed with a comma delimiter, we'll need to remove the commas, separate the sheet names into separate columns (text to columns) then transpose these values(sheet names) into one column (Column A) and then place them into an array which will be dealt with by the rest of the code.

In case you need to, I've made provision in the code for all the source sheet data to be copy/pasted to the Master sheet. Hence the "All" criteria selection.

Sorry to have made this some pretty boring reading for you but it should do the task for you.

I hope that this helps.

Cheerio,
vcoolio.

[Leelabird]

Hello Hans and vcoolio,

You both picked up on the C2 - H3 error I made and guess what.......it works!!
So now that the change event code works I'll test with the code vcoolio supplied and report back. I'll create a button to assign the code to and set up a table of sheet names in a little while. I just have some other matters to attend to with the suits upstairs first.
Vcoolio:-
In these two lines of code:

Code: Select all

Sheet6.[A1:E1].Copy

and

Code: Select all

Sheet6.[A1:E1].Clear

should the E1 actually be F1?

Thank you Hans and vcoolio ever so much for your time and patience. I'll be back to let you know how it all works out.

Kind regards,
Leela.

[vcoolio]

Hi Leela,

Nope, A1:E1 is correct as it refers to the number of worksheets that the TextToColumns function uses. It doesn't refer to the number of data columns (A:F) that are transferred over to the Master sheet.
You could probably change it to, say, A1:O1 which should cover any additional worksheets that you may add in future. It will still work with just the present five worksheets.

I've attached a mock-up sample based on the information you have supplied. Have a play with it before you do anything with your actual workbook just to see if it's what you were hoping for. Even then, if it seems to be what you're after, still test the codes in a copy of your actual workbook.

LeelaTest.xlsm

Cheerio,
vcoolio.

[Leelabird]

Hi Hans and vcoolio,

Again, thank you both so much for the wonderful guidance and thanks vcoolio for the mock-up sample. It is just what I was hoping for.

I created a copy of the workbook to test the codes and it works just brilliantly!! I now have it all set up in the actual workbook and there aren't any problems. Everything works faultlessly (good idea to add the "All" criteria!). I actually set up the table in the List sheet just to keep it out of sight and all works well. The idea of having an expandable table was a good move for future worksheet addition/removal. I won't have to play around with the code anymore.

I really appreciate your hard work.

Kind regards,
Leela.

[vcoolio]

You're welcome Leela. I'm glad that we were able to assist.

Good luck with it all.

Cheerio,
vcoolio.