Copy Data from Sheet to another based on drop-down value

Question

I have several sheets in one workbook. I want to copy-paste the data (entire content) from different sheets to sheet 1 (let's say from B6) based on the drop-down value in 'A2' in Sheet 1. Drop-down consists of names of all the other sheets. So, if I select Sheet 2 in drop-down, it should copy entire content from Sheet 2 to Sheet 1, starting from B6.

Here is the macro, I created for it. But it's not working. Can you help me figure out what's wrong with my code?

Sub Button21_Click()

Dim wb As Workbook

Dim criteria As String

Application.ScreenUpdating = False

    'Set values for your variables.
    Set wb = ThisWorkbook
    criteria = wb.Sheets("Sheet1").Range("A2")
    
Dim TT As ListObject

    For i = 1 To Sheets.Count
    
        With Sheets(i)
            For Each TT In Sheets(i).ListObjects
                If TT.Name = criteria Then TT.Range.Copy Sheets("Sheet1").Range("B6:Q22").PasteSpecial: Exit Sub
                        
            Next
        End With
    Next
Application.ScreenUpdating = True
End Sub

Variatus Variatus · Accepted Answer · 2021-03-08T02:49:40

Here's code that does work.

Sub Button21_Click()

    Dim Wb          As Workbook
    Dim Criteria    As String
    Dim i           As Integer                  ' loop counter: Sheets
    Dim Tbl         As ListObject               ' loop object
    
    'Set values for your variables.
    Set Wb = ThisWorkbook
    Criteria = Wb.Sheets("Sheet1").Range("A2")
        
    Application.ScreenUpdating = False
    For i = 1 To Wb.Sheets.Count                ' { "Shees(1)" is in the ActiveWorkbook
                                                ' { which may be different from Wb
        For Each Tbl In Wb.Sheets(i).ListObjects
            If Tbl.Name = Criteria Then
                Tbl.Range.Copy
                Wb.Worksheets("Sheet1").Range("B6").PasteSpecial
                Exit Sub
            End If
        Next Tbl
    Next i
    Application.ScreenUpdating = True
End Sub

You should declare all variables, not only objects. And it's better to prepare your tools before starting on the job, i.e. declare variables before writing code that uses them.
What's the point of declaring Set Wb = ThisWorkbook if you use the default workbook (= ActiveWorkbook) thereafter?
PasteSpecial needs its own line. The construct you used would deliver the argument for the Copy object's Destination property. The latter is an address and can't include the PasteSpecial method.
It's enough to specify the first cell to paste to.

Note that the ListObject's Range comprises of the entire table. Use the DataBodyRange to specify only data (without headers and totals).

Copy Data from Sheet to another based on drop-down value

1 Answers