1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Insert Worksheet based on cell content

Discussion in 'Business Applications' started by MichaelBuro, Apr 18, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. MichaelBuro

    MichaelBuro Thread Starter

    Joined:
    Apr 18, 2006
    Messages:
    3
    Is there a way to insert a new worksheet based on whether or not a cell contains data and name that worksheet with the contents of the cell.
    Ex: A1= Data
    worksheet would be added and named Data
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,797
    Yes you can do that, but you will need something to "trigger" it like a macro hot key or a command button.
    You can get most of the macro by recording a macro and inserting a worksheet.
    to use A1 you would use something like

    Dim newname as string
    range ("a1").select
    if activecell = "" then exit sub
    newname = activecell
    Sheets.Add.Name = newname
    end sub
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    As OBP said, just to add that you have to be careful that what's in the cell can be used as a valid sheet name. For example:

    Sub AddSheet()
    Dim newname As String
    Application.ScreenUpdating = False
    On Error GoTo 100
    newname = ActiveCell
    Sheets.Add.Name = newname
    Exit Sub
    100:
    MsgBox "Cannot add sheet named " & newname
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub


    Post back if you need help with macros.
     
  4. MichaelBuro

    MichaelBuro Thread Starter

    Joined:
    Apr 18, 2006
    Messages:
    3
    Ok perhaps I was not explicit enough in my wants and needs. My first sheet will contain a series of data names, added either up front (one time only) or on as needed basis. What I am looking to do is automatically add sheets based on the names provided automatically. I tried the macros they work fine, but cannot figure out how to expand the code to include other entries. Is there some sort of reference guide I could use that would explain (I don't even know what you call it -VBA?!?) the information contained within the macros
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK, (be prepared for some trial & error but) let's say:

    1. your workbook contains 3 sheets, "Sheet1" -- "Sheet2" -- "Sheet3"

    2. A1:A5 on Sheet1 contains "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"

    With A1:A5 on Sheet1 selected, you could use something like:

    Sub SheetsCheck()

    x = ActiveSheet.Name
    For Each Cell In Selection
    CheckName = Cell.Value
    For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name = CheckName Then
    Cell.Interior.ColorIndex = 3
    End If
    Next Sheet
    Next Cell

    For Each Cell In Selection
    If Cell.Interior.ColorIndex <> 3 Then
    NewName = Cell.Value
    Sheets.Add
    Sheets(1).Name = NewName
    End If
    Next Cell

    Sheets(x).Select
    For Each Cell In Selection
    Cell.Interior.ColorIndex = 0
    Next Cell

    End Sub

    Basically there are 3 sections to this.

    Section1
    (a) Log the active sheet name (as "x") so that Section3 knows where to return to

    (b) For Each Cell In Selection (i.e. A1:A5) log the cell value (as "CheckName"), then check all sheets in the workbook to see if there's a sheet name that matches CheckName (if there is, colour the cell red ; if there isn't, just proceed to the next cell).

    Section2
    For Each Cell In Selection (still A1:A5 on Sheet1), If Cell.Interior.ColorIndex <> 3 (i.e. if cell isn't red because it's value wasn't found as an existing sheet name in the Section1 check), then:

    NewName = Cell.Value
    Sheets.Add
    Sheets(1).Name = NewName

    (i) log cell value as "NewName" (ii) add a sheet (goes to the "front" of the wb by default) (iii) rename (new) first sheet in wb (Sheets(1)) as "NewName"

    Section3
    Sheets(x).Select (make sure the start sheet is selected), then:

    For Each Cell In Selection
    Cell.Interior.ColorIndex = 0
    Next Cell

    set all cells to no fill.

    So, the code may miss some conditions that I've not foreseen and/or be overkill in some respects, but see how it works for now (NB: save your work before attempting it) then report back, at which point we'll need to look at possible pitfalls (e.g. you can't have a slash in a sheet name, or name a sheet "History").
     
  6. MichaelBuro

    MichaelBuro Thread Starter

    Joined:
    Apr 18, 2006
    Messages:
    3
    Wow that worked but need to make sure I understand some key points. "X" is the name of the sheet containing the data names , but I could call "X" anything I want except history, correct?

    Now for two other questions.
    1) How could I get the added sheets to be formatted in a manner that fits my needs, for example a template complete with fonts, colors, etc.
    2) how do I assign a hot key to activate the macro?
    3) I assumed (at least it worked) that the macros are placed in the General section when using the VBA Editor?
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "x" is NOT the name of the sheet ; "x" is a variable, a "named storage location that can contain data that can be modified during program execution". Except it doesn't physically exist per se, only in memory (or cyberspace, or something -- you're getting into territory where I get lost :eek:)

    Yes, it goes in the "general section" kind of. Which is to say you must have inserted an ordinary (not 'class') module & pasted it in that. Which is correct.

    Hotkey is easy. Press ALT+F8 for the 'Macro' dialog, select it in the list, click 'Options'. Click in the shortcut key box if necessary. Use something like 'M' ('m' shifted), then your 'hotkey' is CTRL+Shift+M.

    I'll have to ponder "sheets to be formatted", you may be able to use Copy instead of Add (i.e. copying a [hidden] sheet that exists in the wb).
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK -- got a bit lost with Help on template sheets cos it says:

    1. Create a workbook that contains one worksheet. On the worksheet, include the formatting, styles, text, and other information you want to appear on all new sheets of the same type.

    2. On the File menu, click Save As.

    3. In the Save as type box, click Template (*.xlt).


    whereas I don't actually have a Template (*.xlt) option.

    But I did get to the stage where I had a template (sheet? ; workbook??) as:

    C:\Documents and Settings\Andy\Application Data\Microsoft\Templates\test.xlt

    containing one sheet named "blahblahblah" with "test" in A1.

    So then I doctored Section2 as:

    For Each Cell In Selection
    If Cell.Interior.ColorIndex <> 3 Then
    NewName = Cell.Value
    Sheets.Add Type:="C:\Documents and Settings\Andy\Application Data\Microsoft\Templates\test.xlt"
    Sheets(1).Name = NewName
    End If
    Next Cell

    Then I ran it as set up before & got 2 new sheets (Sheet4, Sheet5) with "test" in A1 of both.

    So it can be done. You just need to do the same but work into it the path to your template (sheet? ; workbook??)

    HTH,
    Andy
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/460239

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice