How to copy range from one worksheet to another where the worksheet is specified by a cell value?

Question

I am trying to copy data from one worksheet to another. I have a workbook that has about 62 worksheet tabs.

The part that is especially tricky for me is that the worksheet the data needs to be copied to will not always be the same.

I have a dropdown menu that lists 62 different pieces of equipment. This is shown in G1 in the worksheet named "HOME". I want the text to copy over to the correct tab based on the selection.

I figured out how to copy over specific text, when I do this I see the word "TEXT" show up on the specified worksheet.

Sheets(Range("g1").Value).Activate
Range("a1").Value = "TEXT"

I cannot figure out how to copy over G4:G24 from my "HOME" worksheet to another worksheet based on the same drop-down menu.

This is what I tried.

Private Sub CommandButton1_Click()
    Worksheets("HOME").Range("g4:g24").Copy
    Sheets(Range("g1").Value).Activate
    Range("a1").Value = "TEXT"
    Sheets(Range("g1").Value).Activate
    Range("f4").PasteSpecial
End Sub

Answer 1

Be explicit about workbook and worksheets - never use Range/Cells without qualifying a worksheet (though you can get away with it in a worksheet code module if you're referring to the associated worksheet).

Private Sub CommandButton1_Click()
    Dim wb As Workbook, ws As Worksheet
    Set wb = ThisWorkbook                           'or ActiveWorkbook?
    With wb.Worksheets("HOME")
        Set ws = wb.Worksheets(.Range("G1").Value)  'create a worksheet reference
        ws.Range("A1").Value = "TEXT"               '...and use it
        .Range("g4:g24").Copy ws.Range("f4")
    End With
End Sub

See for example: What is the default scope of worksheets and cells and range?

Answer 2

Generally speaking you have a good start there, but it can be accomplished in much fewer lines with much more speed like this:

Sub ExampleSub()

    Dim SheetName As String

    SheetName = Worksheets("HOME").Range("A1").Value

    Worksheets("HOME").Range("G4:G24").Value = Worksheets(SheetName).Range("G4:G24").Value

End Sub

It's not even necessary to use the variable SheetName, but it can help keep things simple, it can also now be reused later in the subroutine. An alternative to reference sheets is to make the variable a worksheet:

Sub ExampleSub()

    Dim SheetName As Worksheet
    Dim HomeSheet As Worksheet

    Set HomeSheet = Worksheets("HOME")
    Set SheetName = Worksheets(HomeSheet.Range("A1").Value)

    HomeSheet.Range("G4:G24").Value = SheetName.Range("G4:G24").Value

End Sub