Run-time error 1004

S

Sus123

New Member
Joined
Aug 14, 2022
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Using VBA macro, I want to copy, rename and protect excel sheet. I have allocated this macro to a button. So every time I click this button a new sheet is created with new name which is next to the previous sheet name and Workbook must be protected in such a manner that sheets created cannot be deleted. I’m getting error when I run my code

Below is my code:



Sub NewPage()

Dim randomNumber As Integer

Dim answer As Integer



' randomNumber = Int(2 + Rnd * (100000 - 2 + 1))

' randomNumber = Rnd() * 10000

With Sheets("Step 1")

If ThisWorkbook.Worksheets.Count < 12 Then

.Copy after:=Sheets("Step 2")

Else

answer = MsgBox("You can only have 10 sheets", vbOKOnly)

Exit Sub

End If

End With

With ActiveSheet

randomNumber = Rnd() * 10000

' .Unprotect Password:="ERiQByLY*dD?4cNfY4u97"

' ActiveWorkbook.Unprotect "ERiQByLY*dD?4cNfY4u97"

Range("C1:D1").ClearContents

Range("C14").ClearContents

Range("F1").ClearContents

Range("H14:G19").ClearContents

Range("A4:A14").ClearContents

Range("G4:G13").ClearContents

With Range("H16").Font

.Name = "Calibri"

.FontStyle = "Bold"

.Size = "16"

End With

With Range("H17").Font

.Name = "Calibri"

.FontStyle = "Bold"

.Size = "13"

End With



.Name = "LB" & "" & randomNumber

With .UsedRange

.Value = .Value

End With

Range("C3:F13").Locked = True

Range("C15:D19").Locked = True

' .Protect Password:="ERiQByLY*dD?4cNfY4u97"

' ActiveWorkbook.Protect "ERiQByLY*dD?4cNfY4u97"

End With



End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi
Try
VBA Code: 
Sub NewPage()
    Dim randomNumber As Integer
    Dim answer As Integer
    ' randomNumber = Int(2 + Rnd * (100000 - 2 + 1))
    ' randomNumber = Rnd() * 10000
    With Sheets("Step 1")
        If ThisWorkbook.Worksheets.Count < 12 Then
            .Copy after:=Sheets("Step 1")
        Else
            answer = MsgBox("You can only have 10 sheets", vbOKOnly)
            Exit Sub
        End If
    End With
    With ActiveSheet
        randomNumber = Rnd() * 10000
        ' .Unprotect Password:="ERiQByLY*dD?4cNfY4u97"
        ' ActiveWorkbook.Unprotect "ERiQByLY*dD?4cNfY4u97"
        Union(Range("C1:D1"), Range("F1"), Range("H14:G19"), Range("A4:A14"), Range("G4:G13")).ClearContents
        With Range("H16").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = "16"
        End With
        With Range("H17").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = "13"
        End With
        .Name = "LB" & "" & randomNumber
        With .UsedRange
            .Value = .Value
        End With
        Range("C3:F13").Locked = True
        Range("C15:D19").Locked = True
        ' .Protect Password:="ERiQByLY*dD?4cNfY4u97"
        ' ActiveWorkbook.Protect "ERiQByLY*dD?4cNfY4u97"
    End With
End Sub
 
Upvote 0
Hi,
welcome to forum

see if this update to your code will do what you want

VBA Code: 
Sub NewPage()
    
    Dim randomNumber    As Long
    
    Const strPassword   As String = "ERiQByLY*dD?4cNfY4u97"
    
    On Error GoTo myerror
    With ThisWorkbook
    
        .Unprotect Password:=strPassword
        
        If .Worksheets.Count < 12 Then
            .Worksheets("Step 1").Copy after:=.Worksheets("Step 2")
        Else
            Err.Raise 600, , "You can only have 10 sheets"
        End If
        
    End With
    
    randomNumber = Rnd() * 10000
    
    With ActiveSheet
        
        .Name = "LB" & "" & randomNumber
        
        Range("A4:A14,C1:D1,C14,F1,H14:G19,G4:G13").ClearContents
        
        With Range("H16:H17").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 16
        End With
        
        Range("H17").Font.Size = 13
        
        With .UsedRange
            .Value = .Value
        End With
        
        Range("C3:F13,C15:D19").Locked = True
    
        .Protect Password:=strPassword
        
    End With
    
myerror:
    ActiveWorkbook.Protect Password:=strPassword
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

Dave
 
Upvote 0
dmt32 said:
Hi,
welcome to forum

see if this update to your code will do what you want

VBA Code: 
Sub NewPage()
   
    Dim randomNumber    As Long
   
    Const strPassword   As String = "ERiQByLY*dD?4cNfY4u97"
   
    On Error GoTo myerror
    With ThisWorkbook
   
        .Unprotect Password:=strPassword
       
        If .Worksheets.Count < 12 Then
            .Worksheets("Step 1").Copy after:=.Worksheets("Step 2")
        Else
            Err.Raise 600, , "You can only have 10 sheets"
        End If
       
    End With
   
    randomNumber = Rnd() * 10000
   
    With ActiveSheet
       
        .Name = "LB" & "" & randomNumber
       
        Range("A4:A14,C1:D1,C14,F1,H14:G19,G4:G13").ClearContents
       
        With Range("H16:H17").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 16
        End With
       
        Range("H17").Font.Size = 13
       
        With .UsedRange
            .Value = .Value
        End With
       
        Range("C3:F13,C15:D19").Locked = True
   
        .Protect Password:=strPassword
       
    End With
   
myerror:
    ActiveWorkbook.Protect Password:=strPassword
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
   
End Sub

Dave
Click to expand...
Hi Dave,

With this code I get the attached error
 

Attachments

  • 2.PNG
    2.PNG
    2.8 KB · Views: 5
Upvote 0
mohadin said:
Hi
Try
VBA Code: 
Sub NewPage()
    Dim randomNumber As Integer
    Dim answer As Integer
    ' randomNumber = Int(2 + Rnd * (100000 - 2 + 1))
    ' randomNumber = Rnd() * 10000
    With Sheets("Step 1")
        If ThisWorkbook.Worksheets.Count < 12 Then
            .Copy after:=Sheets("Step 1")
        Else
            answer = MsgBox("You can only have 10 sheets", vbOKOnly)
            Exit Sub
        End If
    End With
    With ActiveSheet
        randomNumber = Rnd() * 10000
        ' .Unprotect Password:="ERiQByLY*dD?4cNfY4u97"
        ' ActiveWorkbook.Unprotect "ERiQByLY*dD?4cNfY4u97"
        Union(Range("C1:D1"), Range("F1"), Range("H14:G19"), Range("A4:A14"), Range("G4:G13")).ClearContents
        With Range("H16").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = "16"
        End With
        With Range("H17").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = "13"
        End With
        .Name = "LB" & "" & randomNumber
        With .UsedRange
            .Value = .Value
        End With
        Range("C3:F13").Locked = True
        Range("C15:D19").Locked = True
        ' .Protect Password:="ERiQByLY*dD?4cNfY4u97"
        ' ActiveWorkbook.Protect "ERiQByLY*dD?4cNfY4u97"
    End With
End Sub
Click to expand...
Hi Mohadin,

With this code I get the attached error
 

Attachments

  • 1.PNG
    1.PNG
    5 KB · Views: 4
Upvote 0
Sus123 said:
Hi Dave,

With this code I get the attached error
Click to expand...
1) New page = no duplication of sheet number
2) Workbook must be protected in such a manner that sheets created cannot be deleted
 
Upvote 0
Sus123 said:
Hi Mohadin,

With this code I get the attached error
Click to expand...
Can the following be possible



New page = no duplication of sheet number - at present I get the error message attached
Workbook must be protected in such a manner that sheets created cannot be deleted. At present when I protect the workbook the sheet delete menu menu DOES grey out the delete option. HOWEVER, if I protect the workbook, then I cannot create a duplicate sheet - error message attached.
 
Upvote 0
I might be missing something but these lines are generating the same number every time I run it. (on my machine it is 7055)
VBA Code: 
        Dim randomNumber As Integer
        randomNumber = Rnd() * 10000

Which means if you run it a second time this line is going to error out:
VBA Code: 
With ActiveSheet
.Name = "LB" & "" & randomNumber

I am sure it would change if you run it in a loop but it seems to start at that number every time you call the sub.
 
Upvote 0
Sus123 said:
Hi Dave,

With this code I get the attached error
Click to expand...

Hi,

try this update

VBA Code: 
Sub NewPage()
    Dim sheetname       As String
    Dim randomNumber    As Long
    
    Const strPassword   As String = "ERiQByLY*dD?4cNfY4u97"
    
    On Error GoTo myerror
    With ThisWorkbook
        
        .Unprotect Password:=strPassword

        If .Worksheets.Count < 12 Then
            .Worksheets("Step 1").Copy after:=.Worksheets("Step 2")
        Else
            Err.Raise 600, , "You can only have 10 sheets"
        End If
        
    End With
    
    With ActiveSheet
    
        Do
            randomNumber = Rnd() * 10000
            sheetname = "LB" & "" & randomNumber
        Loop Until Not Evaluate("ISREF('" & sheetname & "'!A1)")

        .Name = sheetname
        
        Range("A4:A14,C1:D1,C14,F1,H14:G19,G4:G13").ClearContents
        
        With Range("H16:H17").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 16
        End With
        
        Range("H17").Font.Size = 13
        
        With .UsedRange
            .Value = .Value
        End With
        
        Range("C3:F13,C15:D19").Locked = True
        
        .Protect Password:=strPassword
        
    End With
    
myerror:
    ActiveWorkbook.Protect Password:=strPassword
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

only did a quick test & your random number function seemed to work ok in this instance but may need looking at if problem persists.

Dave
 
Upvote 0
Solution
VBA Code: 
Sub NewPage()
    Dim randomNumber As Integer
    Dim answer As Integer
    ' randomNumber = Int(2 + Rnd * (100000 - 2 + 1))
    ' randomNumber = Rnd() * 10000
    With Sheets("Step 1")
        If ThisWorkbook.Worksheets.Count < 12 Then
            .Copy after:=Sheets("Step 1")
        Else
            answer = MsgBox("You can only have 10 sheets", vbOKOnly)
            Exit Sub
        End If
    End With
    With ActiveSheet
        randomNumber = Rnd() * 10000
         .Unprotect Password:="ERiQByLY*dD?4cNfY4u97" '<<<< Password?
         ActiveWorkbook.Unprotect "ERiQByLY*dD?4cNfY4u97" '<<<< Password?
        Union(Range("C1:D1"), Range("F1"), Range("H14:G19"), Range("A4:A14"), Range("G4:G13")).ClearContents
        With Range("H16").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = "16"
        End With
        With Range("H17").Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = "13"
        End With
        .Name = "LB" & "" & randomNumber
        With .UsedRange
            .Value = .Value
        End With
        Range("C3:F13").Locked = True
        Range("C15:D19").Locked = True
    .Protect Password:="ERiQByLY*dD?4cNfY4u97" '<<<<< Password?
     ActiveWorkbook.Protect "ERiQByLY*dD?4cNfY4u97" '<<<< Password?
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
263
Gajendran Yadhav
G
J
  • Question
Run-time error '1004': The extract range has a missing or invalid field name.
2
Replies
15
Views
694
josros60
J
sparky2205
  • Solved
#BLOCKED! error
2
Replies
10
Views
358
sparky2205
sparky2205
A
  • Question
Import Table Data from Web using VBA to excel Worksheet and able to input Values which can modify the web data to be extract
Replies
0
Views
347
Ambrosia
A
L
  • Solved
VBA Run-time Errors
Replies
3
Views
506
StephenCrump
S

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

Share this page

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
Back
Top