We help IT Professionals succeed at work.

Automatically Creating Tabs

Bright01
Bright01 asked
on
EE Professionals,

Greetings!  I have attached a sample sreadsheet that has 3 Categories.  Each Category has 3 Questions.  From the single Tab, I'm trying to create a macro that wil, when a button is pushed, create 3 new Tabs (as sheets) and auto-populate the sheet with the associated category questions.

That's it!  Thank you in advance.

B.
CreateCatTabsv1.xlsm
Comment
Watch Question

Steven CarnahanAssistant Vice President\Network Manager

Commented:
You could create macro's and assign them to buttons you also create.

Sub Cat_1()
'
' Cat_1 Macro
'

'
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = "Cat 1"
    ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[4]"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[4]"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[4]"
    Range("B1").Select
End Sub


There is probably some refinement that would need to be done but this was just a quick attempt to accomplish what you asked using "Cat 1"  

Author

Commented:
Pony10us,

Thank you for the quick reply.  I tried to use the code and it works well for a single Category.  I need to see how you handle two categories or more in order to figure out how I scale the macro to accomodate 10 Categories and 150 Questions....... also can you put in some comments?  I'm not very good with macros.

Thank you in advance,

B.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
I did not build any error checking into this.

Comments are on the first worksheet describing what is going on with the macro.
 CreateCatTabsv1-1-.xlsm
Glenn RayExcel VBA Developer
Top Expert 2014
Commented:
The following code and workbook assumes that:
1) The list of Categories starts in cell C8 of Sheet1
2) The list of Categories and questions starts in cell E8 of Sheet1
3) The actual category names used will be identical in both lists (necessary to determine sheet names & logic breaks)
4) The number of questions can vary between categories, but there will be no empty rows in the listing in column E
5) Questions will start in cell A1 of each sheet and be added in subsequent rows from thereon.

I added a few extra sample points beyond the original example to show the functionality.

 EE-CreateCatTabsv1.xlsm

If you rename "Sheet1" you will also need to rename it in the code.

-Glenn

Author

Commented:
glenn and Pony10us,

Thank you guys for some excellent work here and I'm learning from you at the same time.  I've combined Pony's code so that in one button, I can push out all the Categories and Questions.  Glenn, if I'm not mistaken, your code allows for me to put a contiguous list together and the code wil do the sort and is particularly effective if I don't have the same number of questions in each category.  So here's what I've got; different categories, not all in a single list, with the same number of questions in each.  I thought I'd use a range name to name each category, then use the range name to place the questions in a new tab.

So here's my last questions are IN CAPS; if I look at Pony's comments;


 Option Explicit                                    
                                    
Sub Cat_2()                                    
'                                    
' Cat_2 Macro                                    
'                                    
                                    
'                                    
    Sheets.Add After:=Sheets(Sheets.Count)                                    Creates a new sheet at the end of the workbook
    Sheets(Sheets.Count).Select                                    
    Sheets(Sheets.Count).Name = "Cat 2"        (IF I WANT TO USE A CELL LOCATION IN THE ORIGINAL SHEET, HOW DO I REFER TO IT?  Sheets(Sheets.Count).???? = "survey!B2" FOR EXAMPLE?
      Renames the sheet (Cat 2 in this case)

WHAT IF I JUST WANT IT TO PASTE A NAMED RANGE INTO THE TAB SO NO COUNTING IS REQUIRED?

    ActiveCell.FormulaR1C1 = "=Sheet1!R[12]C[4]"                                    Selects Cell A1 and then moves down 12 rows down and 4 columns over to select the contents
    Range("A2").Select                                    Places the contents in the new sheet cell A1
    ActiveCell.FormulaR1C1 = "=Sheet1!R[12]C[4]"                                    Repeats the above two lines placing the contents in cell A2 and A3
    Range("A3").Select                                    
    ActiveCell.FormulaR1C1 = "=Sheet1!R[12]C[4]"                                    
    Range("B1").Select                                    Places the cursor in Cell B1

Thanks for hanging with me.

B.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
Using the original workbook you provided you can change the line:

Sheets(Sheets.Count).Name = "Cat 2"

to:

Sheets(Sheets.Count).Name = Sheet1.Range("C8")

This will use the value in cell C8 on the first worksheet for the name of the new sheet

That should answer question #1
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Bright01,  I assumed from your 2nd post that you wanted a scalable utility that would process any number of categories and questions (ex., 10 categories, 150 questions).  My utility would do just that.  

It now sounds like you have various lists and want to refer to them by different range names.  It is possible to copy the cells defined by a given range name as follows:

 
Range("rangename").copy
Sheets("categorysheetname").activate
ActiveSheet.Paste

Open in new window


However, this code would have to be customized for each range of questions you want to copy.  

Can you provide another workbook example showing now how it will look with the various ranges?

-Glenn

Author

Commented:
Pony; Works like a charm.

Glenn; When I create the new tab from a cell reference that Pony has given me, the name of the Tab is now from that cell.  When I use your code and Line 2 "categorysheetname", I get an error since I'm using the same cell reference.  Is there a way to reference the same cell (e.g. Sheet(Survey) B7) in line 2 that would pick up the same tab name it is looking for?

Much thanks,

B.

Author

Commented:
Glenn & Pony,

I got it to work based on your guidance!  Much thanks.  Only one problem.  When it copies the range, it doesn't keep the formatting (cell height/length).  I've attached the test case for you to take a look at.  I think it's probably a very simple change or a line of code (isn't that always the case?? ;-)

Much thanks,

B.
Test-Case-for-generating-tabs.xls
Steven CarnahanAssistant Vice President\Network Manager

Commented:
To copy the size/width you need to modify your macro(s) to include more information.

Sample taken from www.exceltip.com:

Private Sub CopyRowHeigths(TargetRange As Range, SourceRange As Range)
Dim r As Long
    With SourceRange
        For r = 1 To .Rows.Count
            TargetRange.Rows(r).RowHeight = .Rows(r).RowHeight
        Next r
    End With
End Sub

Private Sub CopyColumnWidths(TargetRange As Range, SourceRange As Range)
Dim c As Long
    With SourceRange
        For c = 1 To .Columns.Count
            TargetRange.Columns(c).ColumnWidth = .Columns(c).ColumnWidth
        Next c
    End With
End Sub
Example:
CopyColumnWidths(Range("E1:H1"), Range("A1:D1"))
CopyColumnWidths(Worksheets("Sheet2").Range("A1:D1"), _
    Worksheets("Sheet1").Range("A1:D1"))


Author

Commented:
When I use "PrivateSub" do I have to call the routine or does it automatically adapt?

Do I put this code into the front or back end of the module?

Are the examples you have listed 1 example or 3?

Thank you,

B.

Author

Commented:
Pony,

Here's the code I am using.  I've embedded the code you sent me above in the module; how do I call the right copy code within the Sub AllCat Macro?

Sub AllCat()
'This Macro distributes all Categories into individual Tabs
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = Sheet1.Range("B7")
    Range("Category1").Copy
    ActiveSheet.Paste
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = Sheet1.Range("B24")
    Range("Category2").Copy
    ActiveSheet.Paste
Steven CarnahanAssistant Vice President\Network Manager

Commented:
You need to put the 2 subs at the front and then call them using the examples (there are only 2 - the second one wraps).
Steven CarnahanAssistant Vice President\Network Manager

Commented:
I have not yet had a chance to try to work the 2 codes together to show you. For some reason this morning has been rather busy here at work.   :(  

If you can't get it I will try to play with it a little later.
Assistant Vice President\Network Manager
Commented:
Okay, disregard. Add the lines show below:

Sub AllCat()
'This Macro distributes all Categories into individual Tabs
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = Sheet1.Range("B7")
    Range("Category1").Copy
    Selection.PasteSpecial Paste:=8        ' <----   new
    ActiveSheet.Paste
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = Sheet1.Range("B24")
    Range("Category2").Copy
    Selection.PasteSpecial Paste:=8        ' <-----  new
    ActiveSheet.Paste
   
   
End Sub
 

Author

Commented:
Pony and Glenn,

Great Teamwork!  And thanks so much.  Pony, your final code worked perfectly.
You guys really helped me out with this.  I will be posting a follow-on question that will be much simpler to answer (how to delete the newly created tabs (to start over).  Thanks again!!!!

B.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
Glad we could be of help.