VBA Code - Run Time Error on Renaming a Sheet

[OilEconomist]

Thanks in advance for any suggestions for which I will give feedback.

Why do I get the following run time error when I don't have any of the exceptions to the the name of my worksheet
"Run-time error '1004': You typed an invalid name for a sheet or chart. Make sure that:
-The name that you typed does not exceed 31 characters.
-The name does not contain of the following characters: : \ / ? * [ or ]
-You did not leave the name blank"

Code with error

ActiveSheet.Name = ShtName

and when executed and I want to reference that sheet do I include the quotes when using "ShtName"

                            Sheets("ShtName").Select
                            Worksheets("ShtName").Range("B5").Select

Also does the following code lines look okay? If the cell is NOT (1) blank, or (2) Left four characters does not equal "Date", or the cell does not contain a date in the format of "1/1/2019" or "01/01/2019", I would like to execute the code below it, if not just keep checking all of the cells until it loops around and ends on the lastrow of data.

If Cells(i, 1) <> "" _
                    Or Left(Cells(i, 1).Value, 4) <> "Date" _ 
                    Or Cells(i, 1) <> "Rem." _
                    Or Not IsDate(Cells(i, 1)) Then

The following is the entire code:

Sub ExtractData()


'Dimensioning
    Dim LastRow As Long
    Dim LRPHDW As Long
    Dim StartRow As Long
    Dim i As Long
    Dim ShtName As String




    'Activate the sheet with the data to be copied
        Worksheets("Sheet1").Activate
    
    
    'Find the last row of the entire worksheet for a search stopping point
        LastRow = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, _
              LookIn:=xlFormulas, SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious, MatchCase:=False).Row
     


    'Find the last row of the data to be copied which will be the same for all data that _
            needs to be copied
        LRPHDW = Range("A5").End(xlDown).Offset(1).Row
              LRPHDW = LRPHDW - 2
     
    
    'Select the cell to start the data search (i.e. where the first data set _
            always starts
        Range("A5").Select
        
        
    'Subroutine to find the desired date and copy and paste into a copy of the template worksheet
        
        For i = 1 To LastRow
            Worksheets("Sheet1").Activate
            
            If Cells(i, 1) <> "" _
                    Or Left(Cells(i, 1).Value, 4) <> "Date" _
                    Or Cells(i, 1) <> "Rem." _
                    Or Not IsDate(Cells(i, 1)) Then
                    
                'Store the cell as the name of the worksheet that will be duplicated (Template) _
                   and where the data will pasted
                    ShtName = Cells(i, 1).Value
                
                'Copy the Template worksheet after "SheetB" for the data to be copied and pasted into
                    Sheets("Template").Copy after:=Sheets("Start")
                    ActiveSheet.Name = ShtName
                
                'Copy and paste the data
                    With Sheets("Sheet1")
                        .Range(.Cells(i + 3, 2), .Cells(LRPHDW, 14)).Copy
                    End With


                    Sheets("Start").Select
                    Worksheets("ShtName").Range("B5").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=True
                        
                End If
        
            Next i
        


End Sub

 

[Fluff]

What is the value of ShtName when you get the error?

 

[OilEconomist]

Thanks Fluff for your response!

Test is the value of the cell which will become the sheet name, but I think what's happening is something is wrong with the following line of code:

            If Cells(i, 1) <> "" _
                    Or Left(Cells(i, 1).Value, 4) <> "Date" _
                    Or Cells(i, 1) <> "Rem." _
                    Or Not IsDate(Cells(i, 1)) Then

because when I checked the position of the cursor it was in the cell with a date in the format "4/1/2019" and you can't have a sheet with "/"s.

In that line of code, I'm trying to say if any of those are not TRUE for that particular cell, then copy the date below it. Does something look wrong with that code?

 

[Fluff]

Try changing the Or to And

 

[OilEconomist]

Thanks Fluff as I think that fixed it as sheet gets copied and renamed as "Test", but now it gives me the error "Run Time Error 9' Subscript out of Range" for that same code of line:

Sheets("ShtName").Select

 

[OilEconomist]

Taking the quotes off of "ShtName" in the following lines fixed it.

Sheets(ShtName).Select
Worksheets(ShtName).Range("B5").Select

[Code]

 

[Fluff]

Glad you sorted it & thanks for the feedback.

PS with code tags the one at the end of the code should be [/code], your missing a /