Automatically Creating Tabs

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"  

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.

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 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.

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

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

Select allOpen 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

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.

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

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"))

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.

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

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).

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.

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.

Glad we could be of help.