Using VBA to copy range from one worksheet to another where the worksheet is specified by a cell val-CodePudding

I am trying to use VBA to copy data from one worksheet to another. I have a workbook that has about 62 different 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 currently shown in G1 in the worksheet named "HOME". I want the text to copy over to the correct tab based on the selection.

I have 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"

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

This is what I have 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

CodePudding user response：

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?

CodePudding user response：

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