New sheet creation - expected variable or procedure not module

Question

Good afternoon,

I would like to create the duplicate sheet in my document. Unfortunately I am getting an error:

Expected variable or procedure, not module

I have got the combobox list on my current sheet

Where the code looks as follows:

  Private Sub Cable2_Change()
  Dim rng As Range
  Set rng = ActiveSheet.Range("B65")
  Dim ans As VbMsgBoxResult

  Select Case rng

Case "CFS-PL 107"

        With Sheets("hilti firestopping stores").Range("E5")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Plug").Copy
    ActiveSheet.Range("G18").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-PL 132"

        With Sheets("hilti firestopping stores").Range("E6")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Plug").Copy
    ActiveSheet.Range("G19").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-PL 158"

        With Sheets("hilti firestopping stores").Range("E7")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Plug").Copy
    ActiveSheet.Range("G20").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-PL 202"

        With Sheets("hilti firestopping stores").Range("E8")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Plug").Copy
    ActiveSheet.Range("G21").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-CC"

        With Sheets("hilti firestopping stores").Range("E9")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Cable_Collar").Copy
    ActiveSheet.Range("G22").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-D 25"

        With Sheets("hilti firestopping stores").Range("E10")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Cable_Disc").Copy
    ActiveSheet.Range("G23").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-SL GA1"

        With Sheets("hilti firestopping stores").Range("E11")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Sleeve").Copy
    ActiveSheet.Range("G24").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-SL GA2"

        With Sheets("hilti firestopping stores").Range("E12")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Sleeve").Copy
    ActiveSheet.Range("G25").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-SL GA3"
        With Sheets("hilti firestopping stores").Range("E13")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Sleeve").Copy
    ActiveSheet.Range("G26").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-F FX"
        With Sheets("hilti firestopping stores").Range("E14")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Foam_Blue").Copy
    ActiveSheet.Range("G27").PasteSpecial
    Selection.Name = "Firestop"

 Case "CP 620"
        With Sheets("hilti firestopping stores").Range("E15")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Foam_Red").Copy
    ActiveSheet.Range("G28").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-BL"
        With Sheets("hilti firestopping stores").Range("E16")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Firestop_Block").Copy
    ActiveSheet.Range("G29").PasteSpecial
    Selection.Name = "Firestop"

 Case "CFS-SP SIL"
        With Sheets("hilti firestopping stores").Range("E17")
            .Value = .Value + 1
        End With
    Worksheets("hilti firestopping stores").Shapes("Silicone_Sealant").Copy
    ActiveSheet.Range("G30").PasteSpecial
    Selection.Name = "Firestop"

 Case "Remove"

    ans = MsgBox("Do you want to remove all firestopping elements with their values?", vbQuestion + vbYesNo)
        If ans = vbYes Then
        Sheets("hilti firestopping stores").Range("E5:E17").ClearContents
        Call Firestopshapes
        End If

   End Select
   End Sub

This is the code for my combobox list. Below I have the another code for the new sheet creation:

 Sub CablesSheet()

Dim I As Long
Dim xNumber As Integer
Dim xName As String
Dim xActiveSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Set xActiveSheet = ActiveSheet
xNumber = InputBox("Enter number of times to copy the current sheet")
For I = 1 To xNumber
    xName = ActiveSheet.Name
    xActiveSheet.Copy After:=ActiveWorkbook.Sheets("Cables (Second Floor)")
    ActiveSheet.Name = "Cables (Third Floor)"
  Next
xActiveSheet.Activate
Application.ScreenUpdating = True
End Sub

I found some answer here:

What does the Call keyword do in VB6?

where I found, that it's not good to use Call statement.

I used it once in my code:

  Call Firestopshapes

How can I solve this problem?

Have you named a code module with the same name as one of your procedures? — Tim Williams, Mar 10 '20 at 15:52
No. My code module is "Worksheets" where the Worksheet code is stored. Another one is based within thew sheet, outside of the module — Geographos, Mar 10 '20 at 16:05
In general somewhere here: Worksheets("hilti firestopping stores").Shapes("Firestop_Plug").Copy — Geographos, Mar 10 '20 at 16:19
Did you name a Module "Worksheets" ? You cannot use that name for a module. If I name a module with that name I get the exact same error you're seeing. — Tim Williams, Mar 10 '20 at 16:27

score 1 · Accepted Answer · answered Mar 10 '20 at 16:30

1

You cannot use "Worksheets" as the name for a code module - you will confuse the compiler.

answered Mar 10 '20 at 16:30

Tim Williams

154,628
8
97
125

You were right! I have changed my module name and now works correctly! Thanks a lot! – Geographos Mar 10 '20 at 16:42

New sheet creation - expected variable or procedure not module

1 Answers1