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!

 

[My Aswer Is This]

So in A1 you have a Table Name correct?
And you want this Table copied from what sheet and pasted into what sheet and where on the sheet?
We need the sheet name and the Table name.
And in A2 is what?
Another Table name?

We need to know

 

[My Aswer Is This]

I now know I do not need the sheet name. But are you saying the table name in Range("A1")
Of sheet named "Sheet1"
But I do need to know where you want the table pasted like Sheets("Sheet1").Range("G40")

 

[Ashleimo]

So in A1 you have a Table Name correct?
And you want this Table copied from what sheet and pasted into what sheet and where on the sheet?
We need the sheet name and the Table name.
And in A2 is what?
Another Table name?

We need to know

The table is located on sheet 2 starting in H1 named Indy Q1.

On Sheet 1 in box A1 is a drop down with all the different names of the tables throughout the workbook. I would like when a table name is selected in A1 (Ex. Indy Q1) from the drop down, for it to pull the coordinating table to B1 on sheet 1.

 

[My Aswer Is This]

So this is what I understand you want
On Sheets("Sheet1").Range("A1") you want to enter a Table name like "George"

Then the script will search through all sheets in the workbook. If it finds a Table named "George" in the workbook it will copy Table named "George" into Sheet("Sheet1") Starting in Range("B1")
If this is correct try this. If not please explain more.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab Named Sheet1
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Value
'Modified 3-29-18 8:25 PM EDT
Application.ScreenUpdating = False
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

 

[Ashleimo]

So this is what I understand you want
On Sheets("Sheet1").Range("A1") you want to enter a Table name like "George"

Then the script will search through all sheets in the workbook. If it finds a Table named "George" in the workbook it will copy Table named "George" into Sheet("Sheet1") Starting in Range("B1")
If this is correct try this. If not please explain more.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab Named Sheet1
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Value
'Modified 3-29-18 8:25 PM EDT
Application.ScreenUpdating = False
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

Your description of what I am looking for is spot on. When I enter the code, once I change the selection from the drop down list in A1, I receive an error that states Compile Error: Expected End Sub?

Not sure what I did wrong or how to fix.

 

[Fluff]

Simply add

End Sub

to the end of the code

 

[Ashleimo]

Now getting an error that says Block If without End If.

I am definitely not super experienced with VBA. I have used it successfully in the past, but obviously not a pro.

 

[Fluff]

Add an

End If

just before the End Sub

 

[Ingolf]

Cross posted Populate table from another table based on drop down list selection