Selecting A worksheet by name, I have the name as string

[EitanOrgil]

Hi,

Fairly new to Coding.
I have a code that will get an input from the user, save it as a public variable: "ProjectName".
create a directory using "ProjectName".
copy a template worksheet and name it using "ProjectName".
next The code will open a new form to get more data from the user.
now I want to write the added data to the new worksheet(Now named "ProjectName").
when I try to call the worksheet by it's name using the Public variable, I get an error:
Run-time error '424':
Object required
I need to use it like this as I don't know what the new project name will be.
I cannot access the Worksheet by its number as I later may want to edit the project So I need to call it by it's name.
I don't know how (if possible) to upload an example excel so:
The Excel has 2 worksheets at start:

Sheet1 (Project_Template)​

Sheet2 (Data)​

In Sheet2, cell c3 has this: =max(B:B) (to get a counter/reference number).
I have 1 user Form (UserForm1), in it I have a Multi Page object with 2 pages:

in page 1 I have one text box named txtProjectName and a command button cmdCreateProject​

in page 2 I have 5 text boxes, named txtData1 to txtData5 and a command button btnUpdate​

Here is the code

Public ProjectName

Private Sub cmdCreateProject_Click()
Dim path As String
Dim mydir As String
Dim DataSh As Worksheet
Set DataSh = Sheet2
ProjectName = ""


'error handler
On Error GoTo errHandler:


ProjectName = Me.txtProjectName.Value
If Me.txtProjectName.Value = "" Then
    MsgBox "Please enter a Project Name", vbOKOnly, "Project Name Error"
    Exit Sub
End If
mydir = ThisWorkbook.path & "\" & ProjectName


If Dir(mydir, vbDirectory) = "" Then
    MkDir mydir
    'Copy tamplate sheet to for new Project
    Sheets("Project_Template").Copy After:=Worksheets(Sheets.Count)
    ActiveSheet.Name = ProjectName
Else
    MsgBox "Directory already exsists"
    Me.txtProjectName.Value = ""
    Me.txtProjectName.SetFocus
    ProjectName = ""
    Exit Sub
End If
Set Addme = DataSh.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
DataSh.Activate
DataSh.Select
With DataSh

'add the unique reference ID then all other values
    Addme.Offset(0, -1) = DataSh.Range("C3").Value + 1
    Addme.Value = Me.txtProjectName
End With
Me.MultiPage1.Pages(1).Enabled = True
Me.MultiPage1.Pages(1).Visible = True
Me.MultiPage1.Pages(0).Enabled = Fals
Me.MultiPage1.Pages(0).Visible = Fals
Exit Sub
errHandler:
'if error occurs then show me exactly where the error occurs
MsgBox "Error " & Err.Number & _
" (" & Err.Description & ")in procedure PcmdClear_Click of Form ProjectDB"
End Sub

Private Sub btnUpdate_Click()
Dim WS As Worksheet
Dim Addme As Range
Set WS = ThisWorksheet.Sheets(ProjectName)
Set Addme = WS.Cells(Rows.Count, 3).End(xlUp)
With WS
Addme.Offset(0, 1).Value = Me.txtData1
Addme.Offset(0, 2).Value = Me.txtData2
Addme.Offset(0, 3).Value = Me.txtData3
Addme.Offset(0, 4).Value = Me.txtData4
Addme.Offset(0, 5).Value = Me.txtData5
End With
MsgBox "Contact for Project:" & " " & ProjectName & ", " & "was successfully added"
End Sub

Thank you for any help

Eitan

 

[dmt32]

hi,
quick glance at your code

This

Set WS = ThisWorksheet.Sheets(ProjectName)

Should be this

Set WS = ThisWorkbook.WorkSheets(ProjectName)

However, you also need to ensure that your variable ProjectName has been initialized with a valid sheet name

ProjectName = Me.txtProjectName.Value
Set WS = ThisWorkbook.WorkSheets(ProjectName)

Dave

 

[EitanOrgil]

OK David,

Thanks, that helped...

 

[dmt32]

OK David,

Thanks, that helped...

Glad resolved

Dave