Right so in a nutshell I've got a load of active X command buttons which are used to:
1. Copy a VBA Generated SQL from a Cell (each with a named range with a common prefix followed by a number)
2. Activate the sheet which the user should paste the results of the Copied SQL to
The process they all go through is fairly common so the only thing I have each of the actual button controls doing is setting a suffix number from 1 to 10 which then passes this to a common bit of code:
Option Explicit Private Sub cmdSQL_1_Click() Dim sSQLNumber As String sSQLNumber = 1 Call SQL_To_Clipboard(sSQLNumber) End Sub Sub SQL_To_Clipboard(ByRef sSQLNumber) Dim sSQLName As String Dim sSheetCodeName As String sSQLName = "DynamSQL_" & sSQLNumber sSheetCodeName = "SQL" & sSQLNumber 'Copies the contents of the named range onto the clipboard With New DataObject .SetText DynamSQLOut.Range(sSQLName).Text .PutInClipboard End With 'Activate the relevant worksheet for SQL Output 'THIS IS THE BIT THAT DOESN'T WORK sSheetCodeName.Activate sSheetCodeName.Cells(2, 1).Select End Sub
So the problem is at the end, part where I'm trying to activate the relevant SQL Output Sheet. The codenames of these sheets are all SQL1-10.
I appreciate the above doesn't work because the codename isn't a string so I can't use this to reference the sheet in the same way you could use a string to reference a named range ( i.e. .range(StringVariable) can be used to replace .range("Value of StringVariable") ).
But is there anyway I can get it to recognise a variable as a worksheet codename.
Obviously one solution could be to use the worksheet name rather than the codename and then the above, in theory (if sSheetCodeName was replaced with Sheets(sSheetCodeName) ), would work fine if sSheetCodeName had the same value as the worksheet name. But I can't really do it that way as the worksheets are not generically named for the purposes of the user.
Any ideas folks?