dim sheet 2 as sheet name

[jordanburch]

Hi all,

I have the below. I want it to grab the sheet name of sheet 2 and use it throughout the script. Basically sheet 2 can be called something different every time, but should always be the second sheet in the file. I just need to slightly modify my script to account for that. The sheet name is typically Detail Lines but sometimes they forget the space ect ect.

Thanks,
Jordan

Dim myfile As String
Dim myfile2 As String
Dim erow As Long
Dim filepath As String
Dim filepath2 As String
Dim wb1 As Workbook, wb2 As Workbook
 
 
    Dim fn As String
    Dim fn2 As String
    Dim fn3 As String
    Dim fn4 As String
    Dim ShtName1 As String
    Dim ShtName2 As String
    Dim ShtName3 As String
    ShtName1 = Sheet(2)
    ShtName2 = "Detail"
    ShtName3 = "Detail -"


fn = Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6)
fn2 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 2)
fn3 = Right(ThisWorkbook.Worksheets("Variables").Range("A6").Value, 2)


Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets(1)).Name = "CO SAR"
Set wb1 = ThisWorkbook

fn4 = Right(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 5)
filepath = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\21 Field Details\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & ThisWorkbook.Worksheets("Variables").Range("A1").Value & "\Field Detail Lines\"
'K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\DRP\2020 DRP\2020-05 Reporting Cycle
myfile = "CO21army" & fn4 & ".xlsx"
 
Dim strFileName As String
Dim strFileExists As String

    strFileName = filepath & myfile
    strFileExists = Dir(strFileName)

   If strFileExists = "" Then
           MsgBox "The current month 21 CO SAR file does not exist"
           
    Else
    erow = wb1.Sheets("CO SAR").Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).Row
    Set wb2 = Workbooks.Open(filepath & myfile)
    With wb2
    
     Sheets(2).Select
     With ActiveSheet
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    Else
        If .FilterMode Then
            .ShowAllData
        End If
    End If
    End With
    Dim ShtName As String
ShtName = "Sheet 1"
If Evaluate("isref('" & ShtName & "'!A1)") Then
   'sheet exists do something
Else
   'sheet doesn't exist do something else
End If
   If Evaluate("isref('" & ShtName1 & "'!A1)") Then
    wb2.Sheets(2).Range("q2:q1000").Value = myfile
        .Sheets(2).Range("c2:q1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        
        .Close savechanges:=False
        ElseIf Evaluate("isref('" & ShtName3 & "'!A1)") Then
        .Sheets(2).Range("c2:p1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        .Close savechanges:=False
        
        ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
        .Sheets(2).Range("c2:p1000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        .Close savechanges:=False
        
        End If
        
        
      End With
    End If
   
Application.ScreenUpdating = True
End Sub

 

[GWteB]

Would recommend not using the sheet name at all. If you use an (object) reference it always works. After all, you know the worksheet exists so multiple tests whether the worksheet exists can be omitted.

Dim MySheet as Worksheet
Set MySheet = ThisWorkbook.Sheets(2)

 

[Joe4]

You can do that quite easily like this:

Dim shtName As String
shtName = Sheets(2).Name

but I would recommend using the Worksheet object, like GWteB showed. It makes it even easier to reference in your code.