New worksheet using template based on cell values

[fonix]

I am working on a project where different departments will have to create a variable number of worksheets based on a single template, with different worksheet names. I'm thinking that this would most easily be accomplished by entering the names of the sheets in a single column on a dedicated sheet, and having those cell values used as the name of the new sheet. I have a template file created, but my knowledge of VBA is pretty shallow. Anyone point me in the right direction?

[austenr]

Do you want the sheets created in the template workbook? If so, please post the template.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

Set sh = .Parent.Worksheets.Add
sh.Name = .Cells(i, TEST_COLUMN).Value2
Next i
End With

End Sub
[/vba]

[fonix]

That code worked beautifully! Now I just need to find a way for those new sheets to use the template that I have.

I'm attaching what I have; here's what I'm looking for ultimately. I have the sheet "template" which collects data about enrollment, and sheet "summary" that collects percentage data from sheet "template" (or, ultimately, each created sheet), in row 9 in "summary" from the relevant cell in column L on the template. I am going to put cell values in the template sheet that refer to the sheet name so that I don't have to re-enter formulas in each sheet, and I would like my script to construct another table in the "summary" sheet that refers to the created sheet. So, for example, I need to create 2 sheets with the script, School 1 and School 2, using the template, and two tables in the summary sheet that refer to these new sheets.

Sorry if this is confusing, I'm not the best at putting my ideas into words

(edited to update uploaded file, see post below for file)

[GTO]

If you attached the file in .xls format, you might receive additional answers, as there are still some of us who do not always have access to the converter, much less 2007.

[fonix]

Thanks for the suggestion, here's the .xls... some conditional formatting etc may be broken.

[GTO]

Hi fonix,

Firstly, I just caught that you joined but a few days ago. Let me be the first to welcome you to VBAX. This is a great forum, with, as I have said many times, some mighty nice folks who will go out of their way to help.

Now, I'm not sure how far we'll get, but as you mentioned starting out in vba, I think it would be helpful to get a few basics out of the way.

For instance, while I am not sure where its at in the 2007 vba editor, look for Tools | Options. Once you find the Options dialog, on the 'Editor' tab, tick the checkbox for 'Require variable declaration'.

This will save you countless headaches later, when you waste time debugging because of simply mis-spelling a variable someplace in code you are writing.

Next, while there are no hard rules (at least IMO) as to where to put certain types of procedures, I would suggest the following generally:

Worksheet event procedures of course must stay it the worksheet's module that they are aimed at. Workbook events likewise must be in the ThisWorkbook module.

Private Subs/Functions must be 'visible' to the code calling it, so sometimes you might find it appropriate to place a Private procedure in a sheet's module.

Public procedures, like Bob's suggestion, I would most often place in a Standard module, rather than a sheet's module, or other Object or Class module. As you can see with your button, the sheet's codename must preface the procedure name to call the macro.

'vbax_31698_#6_templatepractice-ms01.xls'!Sheet4.ProcessData

Now if you move the Sub to Module1, and re-assign the button's macro to it, you will see this:

'vbax_31698_#6_templatepractice-ms01.xls'!ProcessData

So while there is nothing 'wrong' per se with planting the code in a sheet's module, I would say that it is far more common to place it in a Standard module. I believe that you will find this practice far less problematic when calling various procedures from other modules.

Now as to your description at #4, I am not crystal clear, but let me see if I understand the first part. You want to create the new sheets based on the "Template" sheet and rename each created sheet as Bob showed. Is that correct?

Mark

[fonix]

Yes, that is correct, and additionally, if possible (this could be done manually pretty easily if need be) to add a table to the 'summary' sheet that references L5-L9, L11-L13 and Z5-Z9, Z11-Z13 for both tables on the 'template' sheet (current and previous year data) to summarize percentages for each created sheet in an easy-to-read format. From what I'm reading, that may most likely be achieved using a pre-defined array that is inserted each time a sheet is created (like I said, I'm new at this :P ). Thanks for the input!

[GTO]

Here is step one. PLease note that we are not checking to see if there are illegal names in Col A, and probably a bigger worry, we are not checking to see if the sheets already exist.

Option Explicit
    
Public Sub CreateSheets()
Dim i       As Long
Dim wks     As Worksheet
    
    With Sheet4 '<---Using codename, or, using sheetname--> ThisWorkbook.Worksheets("Schools")
        
        For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
            '// Copy the Template sheet to the end of the wb, then set a reference to   //
            '// new sheet.  Note that cell O23 will be truncated.                       //
            .Parent.Worksheets("Template").Copy After:=.Parent.Worksheets(.Parent.Worksheets.Count)
            Set wks = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
            '// Rename, then (based on Template currently being protected, but no       //
            '// password required) protect new sheet w/UserInterfaceOnly argument set to//
            '// True.  This way we can get O23 'copied' over correctly.                 //
            wks.Name = .Cells(i, "A").Value2
            wks.Protect Password:="", DrawingObjects:=True, _
                        Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
            '// Get your entire instructions to O23 in the new sheet                    //
            wks.Range("O23").Value = .Parent.Worksheets("Template").Range("O23").Value
        Next
    End With
End Sub

Mark

[fonix]

That's beautiful! I was just trying to figure out the syntax for .Parent.Worksheets and .Copy After, that works wonderfully. I'll comment out the last wks.Range line, as that portion is not particularly important and will be outdated once I figure this out.

As far as adding tables to the 'Summary' worksheet, I'm fleshing out an array to add the table, but I can't seem to think of a way to increment the relevant number of rows to move down after each iteration of the for loop that you posted above. I'm assuming that I'll have to use something like ActiveCell.Offset plus a predefined array... I'm probably way off though :P

[GTO]

...As far as adding tables to the 'Summary' worksheet, I'm fleshing out an array to add the table, but I can't seem to think of a way to increment the relevant number of rows to move down after each iteration of the for loop that you posted above. I'm assuming that I'll have to use something like ActiveCell.Offset plus a predefined array... I'm probably way off though :P

Might I make a suggestion? If I am reading the above correctly, I would suggest that rather than thinking in terms of adding a table per loop, that is that rather than add a table as each sheet is created, maybe we should think of this part as being seperate.

Also, rather than add tables to the pre-existing Summary sheet, I would think more in terms of rebuilding it from scratch. This could be done several ways, but I think the easiest is create a collection of worksheet names, excepting certain ones (Summary, Template, Schools), then define tables, borders, etc and place the formulas in them.

Although it will result in some messy code that will need tuned-up, you could record a macro while laying out one table and the tables values/formulas etc. Then we could look at how to use this along with the collection of worksheets that need data drawn from.

Hope that helps,

Mark

PS. You did stick the code in a Standard Module, right?

[fonix]

I did move the code into a standard module after I uploaded my previous version. I think what I'll probably end up doing is keeping the summary table on each sheet, instead of moving all the summary tables to a summary sheet... that will make all of this MUCH easier, and won't really affect usability that much. If, in the future, users ask for it, I will most likely go through the record-a-macro-and-clean-it-up routine. Thanks a million, your help has been invaluable.

[GTO]

You are most welcome and glad its working

If solved, please mark the thread as such, the option is under Thread Tools atop your first post. This helps other 'answerers' as they do not have to read through the thread only to figure out that it is answered.

Hope I did not make adding tables sound 'too' difficult, I do not think it would be. Certainly post back if you decide to give it a go later

[fonix]

Okay, so I had more time today to work on this, and I basically took your advice and did a single copy/paste routine macro and copied the script, which I then placed in a loop for each sheet. I just can't figure out how to increment the rows to which the copied data is pasted. Below is what I have (WARNING: it's pretty ugly :P) I need to increment the pasted row by 9 rows for each loop.

[VBA]Sub CopyAllTables()
Dim wsheet As Worksheet
Dim i As Long



'Loop through all Worksheets

For Each wsheet In Worksheets

Range("O23:W29").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("C29").Select
Sheets("Template").Select
Range("O23:W23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A1:I1").Select
ActiveSheet.Paste
Sheets("Template").Select
Range("O24:W24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A2:I2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Template").Select
Range("O25:W29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3:I7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Next wsheet




End Sub
[/VBA]

[GTO]

I am afraid I am lost. As you have not qualified the ranges yet, the macro starts out acting against whatever sheet is active, with this part:
[vba] For Each wsheet In Worksheets

Range("O23:W29").Select
Selection.Copy
[/vba]

I am doing too much guessing, but this would appear to be (maybe) copying the instruction cells, which are on the Template or school sheets.

As I think we are wanting to spiffy up the Summary sheet and transpose some data to it, I am unable to follow.

Could you post your wb again, with the code so far, and do this:

• Add one or two fake schools, and complete data entry.
• Manually create the summary sheet, something like you would want it layed out like, to show where the data comes from.
• Include the formulas (at least your previous .xls attachment has all #REF! errors, as I suspect you deleted the sheet the formulas referenced) so that we can see what the "after" should look like.

I have to hit the sack, but should be able to look later tonight or tomorrow evening at latest. That is of course if you have not already received better help from another member.

Mark

[fonix]

Alright, here's what I've got... I haven't done much to the script yet, as I can't seem to get it straight in my mind as to how to approach the issue.

It's not important to me that the values be linked back to the original sheet for each summary table, unless that's somewhat trivial to accomplish; I can have the user re-run the table macro to have the tables updated when they update data on each data entry sheet.

[mdmackillop]

Looking at Post 14.
Use sheet variables to define each sheet you wish to manipulate.
Use the variable to qualify each range
Avoid selecting - just use the references
I've added an offset to show how paste targets can be incremented.

Note that this is not quite the same as your code (I found range locations hard to follow)

[vba]
Option Explicit
Sub CopyAllTables()
Dim wsh As Worksheet
Dim i As Long
Dim Oset As Long
Dim wsTgt As Worksheet
Dim wsTem As Worksheet

Set wsTgt = Sheets("Sheet1")
Set wsTem = Sheets("Template")

For Each wsh In Worksheets
wsh.Range("O23:W29").Copy
wsTgt.Range("A1").Offset(Oset).PasteSpecial Paste:=xlPasteFormats

wsTem.Range("O23:W23").Copy wsTgt.Range("A2").Offset(Oset)

Oset = Oset + 9
Next wsh

End Sub

[/vba]

[fonix]

That's exactly what I'm looking for; the ugliness of the original code comes from it being a raw macro recording. Thanks! I'll apply this to the workbook and see what I can do.

[fonix]

Alright, on to the next clueless question: I'm trying to get the loop to skip the summary, template and schools sheets, and here's what I've got
[VBA]Sub CopyAllTables()
Dim wsh As Worksheet
Dim i As Long
Dim Oset As Long
Dim wsTgt As Worksheet
Dim wsTem As Worksheet

Set wsTgt = Sheets("Sheet1")
Set wsTem = Sheets("Template")

For Each wsh In Worksheets
If wsh = "Summary" Then
Next wsh
ElseIf wsh = "Template" Then
Next wsh
EseIf wsh = "Schools" Then
Next wsh
Else

wsh.Range("O23:W29").Copy
wsTgt.Range("A1").Offset(Oset).PasteSpecial Paste:=xlPasteFormats
wsh.Range("O23:W29").Copy
wsTgt.Range("A2").Offset(Oset).PasteSpecial Paste:=xlPasteValues
'wsTgt.Range("O23:W23").Copy wsTgt.Range("A2").Offset(Oset).PasteSpecial Paste:=xlPasteValues
Oset = Oset + 9
End If
Next wsh

End Sub[/VBA]

It's choking on my first nested if/elseif (shocking); how would I set that up?

[GTO]

Alright, here's what I've got....

Sigh... Did I mention .xls format?