Populate table from another table based on drop down list selection

[Ashleimo]

Essentially what I am trying to do is copy a table from one worksheet to another based on a selection from a drop down list. I would like for each time the drop down option is changed the table is changed.

For example:
Worksheet1
A1=Quarter 1 (Chosen from drop down list)

A2= Table named Quarter 1 pulls from Worksheet 2

Hopefully that is clear. I'm open to all options!

 

[Fluff]

@Ashleimo
Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[My Aswer Is This]

Some how last two lines of my script got cut off.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-30-18 6:45 PM EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Value
Dim TT As ListObject
ans = Target.Value
    For i = 1 To Sheets.Count
    
        With Sheets(i)
            For Each TT In Sheets(i).ListObjects
                If TT.Name = ans Then TT.Range.Copy Sheets(1).Range("B1"): Exit Sub
                    
            Next
        End With
    Next
Application.ScreenUpdating = True
End If
End Sub

 

[Ashleimo]

Some how last two lines of my script got cut off.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-30-18 6:45 PM EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Value
Dim TT As ListObject
ans = Target.Value
    For i = 1 To Sheets.Count
    
        With Sheets(i)
            For Each TT In Sheets(i).ListObjects
                If TT.Name = ans Then TT.Range.Copy Sheets(1).Range("B1"): Exit Sub
                    
            Next
        End With
    Next
Application.ScreenUpdating = True
End If
End Sub

Thank you for your help! I think I may just not be experienced enough to get that to work. Once I run it, nothing happens. I'm not getting an error or anything, just nothing happens.

 

[My Aswer Is This]

So your saying you entered a Table name in range "A1" of the sheet where you entered my script and nothing happened. And your sure you entered a proper table name.

Well here i added a popup message that will popup letting you know the script is running then when you click Ok the script will continue to run. If nothing happens after that it's because the script found no table by that name.

Here try this script.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 4-3-18 6:40 PM EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
MsgBox "Hello now I will look for a Table named  " & Target.Value
Dim ans As String
Dim x As Long
x = 0
ans = Target.Value
Dim TT As ListObject
ans = Target.Value
    For i = 1 To Sheets.Count
    
        With Sheets(i)
            For Each TT In Sheets(i).ListObjects
                If TT.Name = ans Then TT.Range.Copy Sheets(1).Range("B1"): x = x + 1: Exit Sub
                    
            Next
        End With
    Next
    If x = 0 Then MsgBox "I found no table named  " & Target.Value
Application.ScreenUpdating = True
End If
End Sub

 

[Fazza]

if you're still open to other approaches, please consider a parameterised query. uses no formulas & no code

for example http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

suggest you google for further info

 

[Ashleimo]

So your saying you entered a Table name in range "A1" of the sheet where you entered my script and nothing happened. And your sure you entered a proper table name.

Well here i added a popup message that will popup letting you know the script is running then when you click Ok the script will continue to run. If nothing happens after that it's because the script found no table by that name.

Here try this script.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 4-3-18 6:40 PM EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
MsgBox "Hello now I will look for a Table named  " & Target.Value
Dim ans As String
Dim x As Long
x = 0
ans = Target.Value
Dim TT As ListObject
ans = Target.Value
    For i = 1 To Sheets.Count
    
        With Sheets(i)
            For Each TT In Sheets(i).ListObjects
                If TT.Name = ans Then TT.Range.Copy Sheets(1).Range("B1"): x = x + 1: Exit Sub
                    
            Next
        End With
    Next
    If x = 0 Then MsgBox "I found no table named  " & Target.Value
Application.ScreenUpdating = True
End If
End Sub

I had to walk away from this project for a bit but I was finally able to get this to work! Thank you so much for your help!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Ashleimo]

One last question. If there are formulas in the table being pulled, is there a way I can break the formula and only pull the data in the cell? Once I applied your solution, it is pulling the table beautifully, all my formatting is correct, but is giving me a #ref ! in each cell.

 

[Ashleimo]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

One last question. If there are formulas in the table being pulled, is there a way I can break the formula and only pull the data in the cell? Once I applied your solution, it is pulling the table beautifully, all my formatting is correct, but is giving me a #ref ! in each cell.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 5-24-18 6:45 PM EDT
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
MsgBox "Hello now I will look for a Table named  " & Target.Value
Dim ans As String
Dim x As Long
x = 0
ans = Target.Value
Dim TT As ListObject
ans = Target.Value
    For i = 1 To Sheets.Count
    
        With Sheets(i)
            For Each TT In Sheets(i).ListObjects
                If TT.Name = ans Then
                TT.Range.Copy
                Sheets(1).Range("B1").PasteSpecial xlValues
                Sheets(1).Range("B1").PasteSpecial Paste:=xlPasteFormats: x = x + 1
                 Exit Sub
                 End If
                 
            Next
        End With
    Next
    If x = 0 Then MsgBox "I found no table named  " & Target.Value
Application.ScreenUpdating = True
End If
End Sub