New Folder, Workbook, and Worksheet
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. |
New Folder, Workbook, and Worksheet
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" -- Regards Ron de Bruin http://www.rondebruin.nl " wrote in message ... 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. |
All times are GMT +1. The time now is 07:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com