Losing the plot

[VegasNath]

Code: Select all

Sub test()

Dim ws As String ' not worksheet

ws = ActiveSheet.Name & " test"
'Sheets(ws).Select

With Sheets(ws)
    Range("A1") = "test"
End With

End Sub

Why am I getting the "test" result on 'sheet 1' instead of 'sheet 1 test'? It's been a long day...

[HansV]

Because Range("A1") refers to the active sheet. You don't specify that it belongs to Sheets(ws). To do so, change it to .Range("A1") i.e. with a period (dot) before the word Range.

[VegasNath]

Thanks Hans.

[VegasNath]

I also need to check that the ws exists but I don't seem to be able to do so as ws is a string, not a worksheet? Can I have the best of both or will I need 2 declerations?

[HansV]

You could use code like this:

Code: Select all

Dim wsh As Worksheet
On Error Resume Next
Set wsh = Worksheets(ws)
On Error GoTo 0
If wsh Is Nothing Then
  MsgBox "The sheet " & ws & " does not exist!", vbExclamation
  Exit Sub
End If
...

[VegasNath]

Many thanks Hans.

On Error........ This would make for an excellent tutorial!