Reputation: 33
I want to get a string from a text box, make a sheet with the string name and then set variable WS
and use it to put the name of the sheet in the first cell.
Dim WS As Worksheet
Dim nazov As String
nazov = Me.NazovReceptu
Sheets.Add.Name = nazov
Set WS = Worksheets(nazov)
With WS
.Cells(lRow, 1).Value = nazov
End With
'find first empty row in database
lRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
...
I managed to get the string from the text box, make a sheet with the string as the name, but the rest doesn't work. I know that Set WS = Worksheets("nazov")
is bad. I want to make more sheets with different names and use variable WS. How can I do that?
Upvotes: 0
Views: 4747
Reputation: 1110
Try something like this - define WS when you're adding the sheet:
Dim ws As Worksheet
Dim nazov As String
Dim lRow as integer
nazov = "testsheet"
lRow = 1
Set ws = Worksheets.Add
ws.Name = nazov
ws.Cells(lrow, 1).Value = nazov
[...]
Upvotes: 4