Tech Support Guy banner
Status
Not open for further replies.

Insert Worksheet based on cell content

1K views 7 replies 3 participants last post by  bomb #21 
#1 ·
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 ·
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 ·
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 ·
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 ·
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 ·
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 ·
"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 ·
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
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top