VBA Code - Run Time Error on Renaming a Sheet

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
172
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
Code:
ActiveSheet.Name = ShtName
and when executed and I want to reference that sheet do I include the quotes when using "ShtName"

Code:
                            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.

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

The following is the entire code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,323
Office Version
365
Platform
Windows
What is the value of ShtName when you get the error?
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
172
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:

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?
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
172
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:

Code:
Sheets("ShtName").Select
 
Last edited:

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
172
Taking the quotes off of "ShtName" in the following lines fixed it.

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

[Code]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,323
Office Version
365
Platform
Windows
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 /
 

Forum statistics

Threads
1,082,333
Messages
5,364,675
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top