create new workbook, copy data, rename sheet.

Hi all
My aim is to click a commandbutton in a userform with 4 textboxes.
Then a new workbook must be created, the data must be pasted and the sheet must be named after textbox2. After this the sheet must contain the values of the userform textboxes values, then it must save the workbook and name it from textbox1 or activesheet range for textbox1.

This is my current code:

Private Sub CommandButton1_Click()

    Dim ws As Worksheet, i As Long, lRow As Long, FName As String, FPath As String, NewBook As Workbook
    FPath = "c:\pos"
    FName = "sup" & TextBox1.Value
        Set ws = Sheets("supdata")
        Set NewBook = Workbooks.add
        ThisWorkbook.Sheets("supdata").Copy before:=NewBook.Sheets(1)
    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "file " & FPath & "\" & FName & " exists"

    End If
     
            
      '____error handling______


If TextBox1.Value = "" Or nullstring Then
MsgBox "Complete All Information!", vbCritical, "Error..."
GoTo error1
End If
      
      
      
      '____error handling end______
   
    If MsgBox("Add " & TextBox1.Value & " to the database?", vbYesNo, "Confirm add") = vbYes Then
   
      NewBook.Cells(lRow, "A") = TextBox1.Value
      NewBook.Cells(lRow, "B") = TextBox2.Value
      NewBook.Cells(lRow, "C") = TextBox3.Value
      NewBook.Cells(lRow, "D") = TextBox4.Value
      
    
    MsgBox TextBox1.Value & " has been added "
    
    TextBox1.Value = nullstring
    TextBox2.Value = nullstring
    TextBox3.Value = nullstring
    TextBox4.Value = nullstring
    
    NEWACC.Hide
    
        
    Else
    End If
    

    
    ADDSTOCK.Show vbModeless
    
error1:
End Sub

Any help will be appriciated, thanx

Set ws = Sheets("supdata")
Set NewBook = Workbooks.add
ThisWorkbook.Sheets("supdata").Copy before:=NewBook.Sheets(1)

you created a new Workbook, now ThisWorkbook is NewBook
the correct code is

Set ws = ThisWorkbook.Sheets("supdata")
Set NewBook = Workbooks.add
ws.Copy before:=NewBook.Sheets(1)

Hi, patel45,

you´re sure about

you created a new Workbook, now ThisWorkbook is NewBook

ThisWorkbook can only be the workbook with code while ActiveWorkbook can be any active workbook (either ThisWorkbook when the code starts or any other workbook).

@simeonmein

If TextBox1.Value = "" Or nullstring Then
MsgBox "Complete All Information!", vbCritical, "Error..."
GoTo error1
End If

I´d guessed to read it like

If TextBox1.Value = "" Then
  MsgBox "Complete All Information!", vbCritical, "Error..."
  GoTo error1
End If

vbNullString and "" should be very very similar to each other but if you want to compare values from a textbox you would need to mention this in both parts of the comparision (you can´t check one textbox for two conditions unless you use Select Case).

Ciao,
Holger