Consulting

Results 1 to 2 of 2

Thread: Help with crating a tab based on cell value

  1. #1
    VBAX Regular
    Joined
    Mar 2013
    Posts
    45
    Location

    Help with crating a tab based on cell value

    I have the following in my "ThisWorkBook"
    Private Sub Workbook_Open()
    Worksheets("Blank").Activate
    Run "HideRibbon"
    End Sub
    what im trying to do is create a work sheet called 'Report for AA17' (AA17 is in dd-MMM-yy format, but the tab should read 'Report for MMM dd, YYYY') when the workbook is opened, and if the sheet is already created and present dont create...

    thoughts?

    Thank you...

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location

    Add New Sheets In Workbook

    Here is a generic sheet adder I use. It only works in the workbook the code module is in due to the use of "ThisWorkbook." The function always returns Sheets(NewSheetName), (even if it already exists,) and Activates it.


    [vba]Public Function MakeNewSht(NewSheetName As String, AfterSheet As String) As Worksheet
    Dim NewSht As Worksheet

    'Find or make worksheet
    On Error GoTo SheetDoesntExist
    Set NewSht = Sheets(NewSheetName)
    GoTo SheetExists
    SheetDoesntExist:
    Set NewSht = ThisWorkbook.Worksheets.Add(After:=Sheets(AfterSheet))
    NewSht.Name = NewSheetName
    SheetExists:
    NewSht.Activate
    Set MakeNewSht = NewSht
    End Function[/vba]
    for the New Sheet Name, try:
    [vba]
    MyDate = Range("AA17").Value
    NewName = Format(MyDate, "MMM, dd, yyyy")
    newName = "Report For " & NewName[/vba]


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •