adding new worksheets

[MAMIBUSSOL]

I would like the results of my script to be transferred to a new worksheet

firstly I need to check whether the worksheet exist, if the sheet exists then I need to delete it and recreate.

however I also need to add data further on into the code, so I also need to check whether it exist or not

 

[wigi]

For instance:

Sub test()

    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("YOUR SHEET")
    If Err.Number Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "YOUR SHEET"
    End If
    On Error GoTo 0

End Sub

ws is a variable declared as Worksheet. It holds a reference to either the existing worksheet, either the newly created worksheet.

Do not forget to add the name of the worksheet in the code above. If you want, use a constant to hold this name.

 

[MAMIBUSSOL]

how do I adjust this code to put the new sheet at the end

 

[wigi]

Please have a look at the Add method in the VBE help files.

For instance:

Set ws = ThisWorkbook.Worksheets.Add(After:= ThisWorkbook.Worksheets.Count)

 

[MAMIBUSSOL]

I must be going mad, but I just can't see whats wrong with this code, and as it is not creating the required worksheet, I am not getting any errors.

according to my understanding it should do the following

1. Open TEST 2010.XLSM - file does exist and does open
2. creates worksheet 1, LASTNAME, FIRSTNAME - worksheet not created
3. moves the worksheet to the last worksheet - not sure if this works as the worksheet hasn't been created to move

Filename = TEST 2010.XLSM
SHEETNAME = 1, LASTNAME, FIRSTNAME

DIM WS AS WORKSHEET
 
Workbooks.Open Filename:= _
        "C:\Documents and Settings\Admin\Desktop\" & PAYDIR & "\" & Filename
Windows(Filename).Activate 
On Error Resume Next
Set WS = Filename.Worksheets(SHEETNAME)
If Err.Number Then
     Set WS = Filename.Worksheets.Add(AFTER:=Filename.Worksheets.Count)
      WS.Name = SHEETNAME
End If
On Error GoTo 0