VBA Code - Run Time Error on Renaming a Sheet

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

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

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
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
226
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
226
Office Version
  1. 2019
Platform
  1. Windows
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
55,571
Office Version
  1. 365
Platform
  1. 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 /
 

Watch MrExcel Video

Forum statistics

Threads
1,127,616
Messages
5,625,880
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top