VBA to capture Sheetname and use.

[Glasgowsmile]

Hello,

I have a project that is currently only 1 sheet. I'm using Sheets(1) to reference it so I don't have to worry about what it is named. However, in the future, this single sheet may be copied up to 30 times and the sheet names changed each time. Instead of manually changing all of them to Sheets(2), Sheets(3)... etc, how can I capture the Codename or Sheet name with VBA and then use as the active sheet for the rest of my formula?

Does something like this work? I've tried it but with no success so I wonder what I'm missing.

shName = ActiveSheet.Name

 

[Logit]

Option Explicit

Sub WatShtName()
Dim shName As String
    shName = ActiveSheet.Name
    MsgBox shName
End Sub

 

[Glasgowsmile]

Option Explicit

Sub WatShtName()
Dim shName As String
    shName = ActiveSheet.Name
    MsgBox shName
End Sub

How would I add shName into another variable? I get a type mismatch error when trying the below.

I added your code above and wanted to do the following because I'm using sh in a with statement further down.

Dim shName As String
Dim sh As Worksheet

Name = ActiveSheet.Name

sh = Name

 

[Logit]

.
[ICODE] [CODE=vba]Option Explicit Sub shVar() Dim sh As Worksheet Set sh = ActiveSheet MsgBox sh.Name With sh Range("B2").Value = "Hello" End With End Sub[/CODE] [/ICODE]

 

[Glasgowsmile]

How would I add shName into another variable? I get a type mismatch error when trying the below.

I added your code above and wanted to do the following because I'm using sh in a with statement further down.

Dim shName As String
Dim sh As Worksheet

Name = ActiveSheet.Name

sh = Name

so I did this:

Set sh = ActiveSheet

This works great, however, I'm having issues with my clear content vba. This is what it was

  Sheet1.Range("D5:O5, A7:O109, A200").ClearContents

I don't know what to do with it now to make it work on the active sheet only. Any ideas?

I tried the following but neither option worked out. It does clear content, but it clears it on the sheets1instead of what I'm actively using.

sh.Range("D5:O5, A7:O109, A200").ClearContents
ActiveSheet.Range("D5:O5, A7:O109, A200").ClearContents

Update: I don't know what the heck is going on, I re-opened Excel and the sh.Range command works for clearing content now. Bizzare issue but thanks for the help @Logit

 

[Logit]

.
See if this works :

Option Explicit

Sub shVar()
Dim sh As Worksheet

Set sh = ActiveSheet

With sh

    .Range("D5:O5, A7:O109, A200").ClearContents

End With

End Sub