Macro to Create a New Book and Rename

[squeakums]

I have a written macro that works but one little issue. It is setup to create a new excel worksheet (BOOK1) and then rename (BOOK1) to current date. Although, the user of the macro has decided that he/she wants to run it several times while its still open. In doing that the next time it tries to run it creates (BOOK2), not (BOOK1). I explained he/she would have to close the macro and reopen it and run it again so that it doesn't error our (which it does, if he/she keeps it open and runs it again, because it cannot rename (BOOK1) since (BOOK2) is now the next spreadsheet. How can I either tell it to always create (BOOK1) or change the other parts of the macro to tell it to take the current active spreadsheet and rename tab what I want it to be.

**apparently I have to make the macro super easy friendly...

Thanks.

 

[Hermanie]

You can build a check within the code to see if the workbook with a specific name (in your case the date) is already open, if so activate that workbook, if not create it.

 

[squeakums]

I wouldn't want it to re-activate the same worksheet it would need to create in another spreadsheet (BOOK1) which doesn't exist anymore since it was renamed.

 

[Fluff]

Could you please supply your code?

 

[squeakums]

Sure, here it is:

Sub Macro2()
'
' Macro2 Macro
'
    Workbooks.Add
    Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
    Windows("Book1").Activate
    Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
    Sheets("Ariba Tree").Select
    Range("A1:M1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book1").Activate
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    ActiveWindow.DisplayGridlines = False
    Range("A1").Select
    Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
    Windows("Book1").Activate
    Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
    Columns("A:M").Select
    Sheets("Ariba Tree").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book1").Activate
    Columns("A:M").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
    Application.WindowState = xlMinimized
    Windows("Book1").Activate
    Columns("A:M").Select
    Selection.AutoFilter
    Range("A2").Select
    Application.WindowState = xlNormal
    Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
    Range("A2").Select
    Windows("Book1").Activate
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Ariba Tree"
    Range("A1").Select
    ActiveSheet.Name = ActiveSheet.Name & " " & Format(Date, "(mm-dd-yy)")

Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
Sheets("Macro").Select
MsgBox "Macro Done!"
'
End Sub

 

[Fluff]

When posting code please use code tags (the # icon in the reply window).

Try

Sub Macro2()

   Dim Wbk As Workbook
   
   Set Wbk = Workbooks.Add
   
   Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
   Wbk.Activate
   Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
   Sheets("Ariba Tree").Select
   Range("A1:M1").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy
   Wbk.Activate
   Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
      , SkipBlanks:=False, Transpose:=False
   ActiveWindow.DisplayGridlines = False
   Range("A1").Select
   Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
   Wbk.Activate
   Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
   Columns("A:M").Select
   Sheets("Ariba Tree").Select
   Application.CutCopyMode = False
   Selection.Copy
   Wbk.Activate
   Columns("A:M").Select
   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
   Range("A1").Select
   Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
   Application.WindowState = xlMinimized
   Wbk.Activate
   Columns("A:M").Select
   Selection.AutoFilter
   Range("A2").Select
   Application.WindowState = xlNormal
   Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
   Range("A2").Select
   Wbk.Activate
   Sheets("Sheet1").Select
   Sheets("Sheet1").Name = "Ariba Tree"
   Range("A1").Select
   ActiveSheet.Name = ActiveSheet.Name & " " & Format(Date, "(mm-dd-yy)")
   
   Windows("Macro Ariba Tree Master and Sub.xlsm").Activate
   Sheets("Macro").Select
   MsgBox "Macro Done!"
'
End Sub