VBA to Create new tabs based on Location

[miroy72]

Hi, I am new to this forum and kinda new to Macros. Hope somebody can help me.

I have a excel 2010 spreadsheet (~70000 rows), with lets say 10 columns, first column is LOCATION (there are about 30 locations) 6th column is CRIME TYPE, 7th column is RACE, 8th column is AGE GROUP, and 10th column is CURRENT AGE.

Now I would like a macro that would look at the location column, and create new tabs for each location in a template format (see below) and provide counts by Location, Crime Type, Race, Age Group, and average Current Age.

Each tab should have 2 separate templates (see below)

Thanks! I greatly appreciate any help with this!!!



Capture.JPG

[SamT]

When you say "Tabs," you should say "WorkSheets," but "Sheets" is acceptable. A Tab is those things under the WorkSheets.

First, create a single WorkSheet with the two Tables ("Templates"" on it, side be side, Make sure that the "Race" and "Age" sections are in the same Row(s.) Name that Worksheet"CrimeReportTemplate"

In VBA, insert a Module and name it "modTableDescriptors"
In the Module create Constants for each use Row in the Table(s)

Examples:

Const rwBlack As Long = 6
Const rwWhite As Long = 7
'
'
'
Const rwTotalCrimes As long = 3
Const rwPerCentOfTotal As Long = 4
'
'
'
Const rwCurrentAveAge As Long = 20

Then repeat the same thing for the Column Letters, (or numbers)

Examples:

Const colPopulation As String = "B"
Const colPerCentFacility As String = "C"
Const colViolent As Long = 6
Const colProp_PubDisOrder As Long = 7

You get the picture. Get back to use when you're done with that.

[miroy72]

Here is what I have done according to your instructions... thank you very much!!

Step1.JPG

[SamT]

Well done. Now, if you click the "Go Advanced" button, it will open the Advanced Message Editor. Below that will be a "Manage Attachments" button which will let you upload a Workbook.

You see. it's really hard to copy all that from a screenshot

[miroy72]

DemTry.xlsmHere it is!

[SamT]

Whoops! I forgot. Upload a copy or the master list workbook with a row for each "Location" in it and all personal identifying items removed (Name ID Number, etc.) We need that list of all "Locations."

Tell us the exact names of the master list book and its full path (Example "C:\Prisons\facilities\data\Arpaio's List.xlsx")

I think what we are going to do is: after copying the Template we will add the Location Name to "A1" in the copy, then just use Formulas that reference "A1" to Fill in the Blanks.

We will actually put the formulas in the Template, but each formula will start =IF($A$1="","",Fill-In-The-blanks) to keep the Template empty.

@ All Members: Can a Name be used as a Workbook reference in a Formula? (=MasterBook Sheet1!A1)

[miroy72]

SamT, I think I am over my head on this one. I thought of another way to create my report. I really do appreciate your willingness to help!!


I have found a way around for my report. Would you be able to help me with this macro?

I am trying to find a macro to copy my worksheet that is called "Sheet3" and rename the worksheets based on a list of locations that is in "Sheet1" worksheet starting in A1.

In addition I would like to write "June 30,2014 "Location name"" in A1 of each worksheet that was created based on the same Location list in "Sheet1" worksheet. (It can be without this last step as well if that complicates things)


Thanks again for all your help!!

[SamT]

This will do

Sub VBAX_SAMT_MakeReportSheets()
'Makes new Sheets and names them by names in "A:A"
'For help see: http://www.vbaexpress.com/forum/showthread.php?54121

Dim Cel As Range
Dim Sht As Worksheet

  For Each Cel In Sheets("Sheet3").Range("A1").CurrentRegion
    Worksheets.Add
    With ActiveSheet
      .Range("A1") = Format(Date, "mmm dd, yyyy") & " " & Cel
      .Name = Cel.Value
    End With
  Next Cel
End Sub

If you need to reference the Sheet Name in your formulas, you can use this

Sub VBAX_SAMT_MakeReportSheets()
'Makes new Sheets and names them by names in "A:A"
'For help see: http://www.vbaexpress.com/forum/showthread.php?54121

Dim Cel As Range
Dim Sht As Worksheet

  For Each Cel In Sheets("Sheet3").Range("A1").CurrentRegion
    Worksheets.Add
    With ActiveSheet
      .Range("A1") = Format(Date, "mmm dd, yyyy") & " " & Cel
      .Name = Cel.Value
      .Names.Add Name:=Cel.Text & "!ShtLoc", RefersTo:=Cel.Text & "!"
    End With
  Next Cel
End Sub

And use formulas like

=VLOOKUP(ShtLoc,MasterList!A1:B2,2)

Where "ShtLoc" will always return the name of the WorkSheet the Formula is in. (Only in sheets created by this code.)

[miroy72]

Will try this. Thank you very much!