New Folder, Workbook, and Worksheet

[marston.gould]

I'm trying to figure out a way to create a routine that when activated
will

1) Place a new folder on the desktop

When another situation is true

2) Create a new workbook with a name that is in a string

When another situation is true

3) Add a new worksheet in the workbook with a name that is held in a
string

My biggest struggle is the last item. The only routine I've found to
rename a worksheet requires that I know the name of the active
worksheet. But everytime I add a new worksheet it gets indexed with a
new name (e.g. Sheet1, Sheet2, Sheet3, etc.). Do I have to track this
or is there an easier way.

 

[Ron de Bruin]

Hi

1 + 2

Add folder and add/save a workbook in that folder

Sub test()
Dim wsh As Object
Dim fs As Object
Dim DesktopPath As String
Dim DirString As String
Dim fname As String
Dim wb As Workbook

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DesktopPath = wsh.SpecialFolders.Item("Desktop")
DirString = DesktopPath & "\Testfolder"

If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
Else
End If

Application.ScreenUpdating = False
fname = DirString & "\Ron.xls"
Workbooks.Add xlWBATWorksheet
Set wb = ActiveWorkbook
With wb
.SaveAs fname
.Close False
End With
Application.ScreenUpdating = True
End Sub



3)
Worksheets.Add.Name = "Ron"