Mixing
Selecting A worksheet by name, I have the name as string
Fairly new to vba code, need some help...
I have a VBA code that copies a template sheet and renames it, I have the new name saved as a public string:
"My_Tamplate" is the sheet I copy from
"PublicStringName" is the public string variable I use to rename it to
I also use the "PublicStringName" in other places in the form, this is whay I needed it as a string.
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.name = PublicStringName
Next when I need to write the data I collected using a form, I want to write it to the newly created sheet.
next I open a new form, collect data from the user to several variables.
Now I want to write the data into the newly created sheet (now named "PublicStringName")
If I declare WS as worksheet, it will not accept "PublicStringName" as it is a string (I think).
I do not know what sheet number it will be so I cannot call it by (sheet1) for example.
Thanks in advanced
Eitan
Update - 06 JAN 19
I don't know how to upload my example excel, so:
The excel has 2 sheets named: Data (sheet2) and Project_Template (Sheet1)
In sheet2 C3 I have =MAX(B:B)
I have 1 form (UserForm1), it has a multi page object
in page 1 I have a text box txtProjectName and a button cmdCreateProject
in page 2 I have 5 text boxes, named: txtData1 to txtData5 and an Update button btnUpdate
I try'd PeterT solution (here in the code)
attaching the problematic code here:
Public ProjectName
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
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
Now when I try to update the newly created Worksheet (named after the project) I get the error: Run-time error '424':
Object required
on the line:
Set WS = ThisWorksheet.Sheets(ProjectName)
I think that Gary's Student answer is missing the step where After I create the new sheet I start collecting the data
Hope that I made it more clear
excel vba worksheet-function
asked Jan 2 at 16:05
EitanEitan
154
add a comment |
Fairly new to vba code, need some help...
I have a VBA code that copies a template sheet and renames it, I have the new name saved as a public string:
"My_Tamplate" is the sheet I copy from
"PublicStringName" is the public string variable I use to rename it to
I also use the "PublicStringName" in other places in the form, this is whay I needed it as a string.
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.name = PublicStringName
Next when I need to write the data I collected using a form, I want to write it to the newly created sheet.
next I open a new form, collect data from the user to several variables.
Now I want to write the data into the newly created sheet (now named "PublicStringName")
If I declare WS as worksheet, it will not accept "PublicStringName" as it is a string (I think).
I do not know what sheet number it will be so I cannot call it by (sheet1) for example.
Thanks in advanced
Eitan
Update - 06 JAN 19
I don't know how to upload my example excel, so:
The excel has 2 sheets named: Data (sheet2) and Project_Template (Sheet1)
In sheet2 C3 I have =MAX(B:B)
I have 1 form (UserForm1), it has a multi page object
in page 1 I have a text box txtProjectName and a button cmdCreateProject
in page 2 I have 5 text boxes, named: txtData1 to txtData5 and an Update button btnUpdate
I try'd PeterT solution (here in the code)
attaching the problematic code here:
Public ProjectName
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
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
Now when I try to update the newly created Worksheet (named after the project) I get the error: Run-time error '424':
Object required
on the line:
Set WS = ThisWorksheet.Sheets(ProjectName)
I think that Gary's Student answer is missing the step where After I create the new sheet I start collecting the data
Hope that I made it more clear
excel vba worksheet-function
asked Jan 2 at 16:05
EitanEitan
154
2
After you create the sheet, you can certainly useDim ws As WorksheetandSet ws = ThisWorkbook.Sheets(PublicStringName)to give you access to your new sheet.
– PeterT
Jan 2 at 16:19
1
OrSet ws = ThisWorkbook.Worksheets(Sheets.Count)after the copy.
– Scott Craner
Jan 2 at 16:22
Did not help, getting the error: Run-time error '424': Object required
– Eitan
Jan 6 at 12:33
add a comment |
Fairly new to vba code, need some help...
I have a VBA code that copies a template sheet and renames it, I have the new name saved as a public string:
"My_Tamplate" is the sheet I copy from
"PublicStringName" is the public string variable I use to rename it to
I also use the "PublicStringName" in other places in the form, this is whay I needed it as a string.
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.name = PublicStringName
Next when I need to write the data I collected using a form, I want to write it to the newly created sheet.
next I open a new form, collect data from the user to several variables.
Now I want to write the data into the newly created sheet (now named "PublicStringName")
If I declare WS as worksheet, it will not accept "PublicStringName" as it is a string (I think).
I do not know what sheet number it will be so I cannot call it by (sheet1) for example.
Thanks in advanced
Eitan
Update - 06 JAN 19
I don't know how to upload my example excel, so:
The excel has 2 sheets named: Data (sheet2) and Project_Template (Sheet1)
In sheet2 C3 I have =MAX(B:B)
I have 1 form (UserForm1), it has a multi page object
in page 1 I have a text box txtProjectName and a button cmdCreateProject
in page 2 I have 5 text boxes, named: txtData1 to txtData5 and an Update button btnUpdate
I try'd PeterT solution (here in the code)
attaching the problematic code here:
Public ProjectName
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
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
Now when I try to update the newly created Worksheet (named after the project) I get the error: Run-time error '424':
Object required
on the line:
Set WS = ThisWorksheet.Sheets(ProjectName)
I think that Gary's Student answer is missing the step where After I create the new sheet I start collecting the data
Hope that I made it more clear
excel vba worksheet-function
asked Jan 2 at 16:05
EitanEitan
154
Fairly new to vba code, need some help...
I have a VBA code that copies a template sheet and renames it, I have the new name saved as a public string:
"My_Tamplate" is the sheet I copy from
"PublicStringName" is the public string variable I use to rename it to
I also use the "PublicStringName" in other places in the form, this is whay I needed it as a string.
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.name = PublicStringName
Next when I need to write the data I collected using a form, I want to write it to the newly created sheet.
next I open a new form, collect data from the user to several variables.
Now I want to write the data into the newly created sheet (now named "PublicStringName")
If I declare WS as worksheet, it will not accept "PublicStringName" as it is a string (I think).
I do not know what sheet number it will be so I cannot call it by (sheet1) for example.
Thanks in advanced
Eitan
Update - 06 JAN 19
I don't know how to upload my example excel, so:
The excel has 2 sheets named: Data (sheet2) and Project_Template (Sheet1)
In sheet2 C3 I have =MAX(B:B)
I have 1 form (UserForm1), it has a multi page object
in page 1 I have a text box txtProjectName and a button cmdCreateProject
in page 2 I have 5 text boxes, named: txtData1 to txtData5 and an Update button btnUpdate
I try'd PeterT solution (here in the code)
attaching the problematic code here:
Public ProjectName
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
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
Now when I try to update the newly created Worksheet (named after the project) I get the error: Run-time error '424':
Object required
on the line:
Set WS = ThisWorksheet.Sheets(ProjectName)
I think that Gary's Student answer is missing the step where After I create the new sheet I start collecting the data
Hope that I made it more clear
excel vba worksheet-function
excel vba worksheet-function
asked Jan 2 at 16:05
EitanEitan
154
asked Jan 2 at 16:05
EitanEitan
154
edited Jan 6 at 9:38
Eitan
asked Jan 2 at 16:05
EitanEitan
154
asked Jan 2 at 16:05
EitanEitan
154
asked Jan 2 at 16:05
EitanEitan
154
154
2
After you create the sheet, you can certainly useDim ws As WorksheetandSet ws = ThisWorkbook.Sheets(PublicStringName)to give you access to your new sheet.
– PeterT
Jan 2 at 16:19
1
OrSet ws = ThisWorkbook.Worksheets(Sheets.Count)after the copy.
– Scott Craner
Jan 2 at 16:22
Did not help, getting the error: Run-time error '424': Object required
– Eitan
Jan 6 at 12:33
add a comment |
2
After you create the sheet, you can certainly useDim ws As WorksheetandSet ws = ThisWorkbook.Sheets(PublicStringName)to give you access to your new sheet.
– PeterT
Jan 2 at 16:19
1
OrSet ws = ThisWorkbook.Worksheets(Sheets.Count)after the copy.
– Scott Craner
Jan 2 at 16:22
Did not help, getting the error: Run-time error '424': Object required
– Eitan
Jan 6 at 12:33
2
2
After you create the sheet, you can certainly use
Dim ws As Worksheet and Set ws = ThisWorkbook.Sheets(PublicStringName) to give you access to your new sheet.– PeterT
Jan 2 at 16:19
After you create the sheet, you can certainly use
Dim ws As Worksheet and Set ws = ThisWorkbook.Sheets(PublicStringName) to give you access to your new sheet.– PeterT
Jan 2 at 16:19
1
1
Or
Set ws = ThisWorkbook.Worksheets(Sheets.Count) after the copy.– Scott Craner
Jan 2 at 16:22
Or
Set ws = ThisWorkbook.Worksheets(Sheets.Count) after the copy.– Scott Craner
Jan 2 at 16:22
Did not help, getting the error: Run-time error '424': Object required
– Eitan
Jan 6 at 12:33
Did not help, getting the error: Run-time error '424': Object required
– Eitan
Jan 6 at 12:33
add a comment |
1 Answer
1
active
oldest
votes
Here is one way to use the Name:
Sub FreshSheet()
Dim PublicStringNAme As String
PublicStringNAme = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringNAme
'
'
'
'
Sheets(PublicStringNAme).Range("A1") = 1
End Sub
If you want to make the variable "more Global" then:
Public PublicStringName As String
Sub FreshSheet()
PublicStringName = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringName
'
'
'
'
Sheets(PublicStringName).Range("A1") = 1
End Sub
See:
Scope
answered Jan 2 at 16:23
Gary's StudentGary's Student
74.9k94164
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009487%2fselecting-a-worksheet-by-name-i-have-the-name-as-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Here is one way to use the Name:
Sub FreshSheet()
Dim PublicStringNAme As String
PublicStringNAme = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringNAme
'
'
'
'
Sheets(PublicStringNAme).Range("A1") = 1
End Sub
If you want to make the variable "more Global" then:
Public PublicStringName As String
Sub FreshSheet()
PublicStringName = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringName
'
'
'
'
Sheets(PublicStringName).Range("A1") = 1
End Sub
See:
Scope
answered Jan 2 at 16:23
Gary's StudentGary's Student
74.9k94164
add a comment |
Here is one way to use the Name:
Sub FreshSheet()
Dim PublicStringNAme As String
PublicStringNAme = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringNAme
'
'
'
'
Sheets(PublicStringNAme).Range("A1") = 1
End Sub
If you want to make the variable "more Global" then:
Public PublicStringName As String
Sub FreshSheet()
PublicStringName = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringName
'
'
'
'
Sheets(PublicStringName).Range("A1") = 1
End Sub
See:
Scope
answered Jan 2 at 16:23
Gary's StudentGary's Student
74.9k94164
add a comment |
Here is one way to use the Name:
Sub FreshSheet()
Dim PublicStringNAme As String
PublicStringNAme = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringNAme
'
'
'
'
Sheets(PublicStringNAme).Range("A1") = 1
End Sub
If you want to make the variable "more Global" then:
Public PublicStringName As String
Sub FreshSheet()
PublicStringName = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringName
'
'
'
'
Sheets(PublicStringName).Range("A1") = 1
End Sub
See:
Scope
answered Jan 2 at 16:23
Gary's StudentGary's Student
74.9k94164
Here is one way to use the Name:
Sub FreshSheet()
Dim PublicStringNAme As String
PublicStringNAme = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringNAme
'
'
'
'
Sheets(PublicStringNAme).Range("A1") = 1
End Sub
If you want to make the variable "more Global" then:
Public PublicStringName As String
Sub FreshSheet()
PublicStringName = "Ellan"
Sheets("My_Tamplate").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = PublicStringName
'
'
'
'
Sheets(PublicStringName).Range("A1") = 1
End Sub
See:
Scope
answered Jan 2 at 16:23
Gary's StudentGary's Student
74.9k94164
edited Jan 2 at 16:30
answered Jan 2 at 16:23
Gary's StudentGary's Student
74.9k94164
answered Jan 2 at 16:23
Gary's StudentGary's Student
74.9k94164
answered Jan 2 at 16:23
Gary's StudentGary's Student
74.9k94164
74.9k94164
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009487%2fselecting-a-worksheet-by-name-i-have-the-name-as-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
After you create the sheet, you can certainly use
Dim ws As WorksheetandSet ws = ThisWorkbook.Sheets(PublicStringName)to give you access to your new sheet.– PeterT
Jan 2 at 16:19
1
Or
Set ws = ThisWorkbook.Worksheets(Sheets.Count)after the copy.– Scott Craner
Jan 2 at 16:22
Did not help, getting the error: Run-time error '424': Object required
– Eitan
Jan 6 at 12:33