CODEWITHSUNDEEP
vbaexcel
lielo
lielo

Reputation: 33

set variable in Worksheets()

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

Answers (1)

MattCrum
MattCrum

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

Related Questions