How to use a cell value as an input for a sheet name in VBA

[Nicholas3108]

I am trying to copy data from one worksheet to another using VBA. This is the code I currently have:

Sub sbCopyRangeToAnotherSheet()
Sheets("Active Worksheet, A2").Range("Q1:S52").Copy Destination:=Sheets("Analyst").Range("B3")
End Sub

I am trying to link the sheet name in my VBA code to the value/text in cell A2 of the active worksheet (I am going to have a dropdown of the different names of the worksheets within the workbook from where I want to take data).

Each time I run the macro I get the error code "Subscript out of range".

Would greatly appreciate some assistance on this.

 

[Norie]

Try this.

Sub sbCopyRangeToAnotherSheet()
Dim strSheet As String

    strSheet = ActiveSheet.Range("A2").Value
    Sheets(strSheet).Range("Q1:S52").Copy Destination:=Sheets("Analyst").Range("B3")

End Sub

 

[Nicholas3108]

Thanks for your help Norie - worked perfectly!