Ignoramus 5,794 Posted August 1, 2013 Share Posted August 1, 2013 The extent of my macro-writing ability comes from cobbling together recorded macros and google search results so I'm running into a wall.I have a worksheet named "LOCATIONS" that can have a variable number of unique location names (one week there may be ten locations on the list, the next week twenty different ones). Example:0142 - DENVER0687 - HOUSTON1374 - DALLAS655A - NEW ORLEANS8743 - SAN DIEGOEtc...I need a macro that will step through the list and for each location name, create a new worksheet and name that sheet for the location. Link to post Share on other sites
Titans_fan 118 Posted August 1, 2013 Share Posted August 1, 2013 Do you want a new sheet every week regardless of if you have one already? Per your example, if you have that list this week and next week contains Denver and New Orleans but adds Detroit and Cleveland, do you just want Detroit and Cleveland or all four... Link to post Share on other sites
Ignoramus 5,794 Posted August 1, 2013 Author Share Posted August 1, 2013 Do you want a new sheet every week regardless of if you have one already? Per your example, if you have that list this week and next week contains Denver and New Orleans but adds Detroit and Cleveland, do you just want Detroit and Cleveland or all four...There will be a completely new data file each week (not appended or anything). The spreadsheet should contain only sheets for locations that have entries that week. Link to post Share on other sites
Thorpe 218 Posted August 1, 2013 Share Posted August 1, 2013 Copy this into the workbook code module. The sheet with the names should be sheet 1, column 1Sub MakeNewSheets()Dim i As Long, sht As Worksheet'200 is an arbitrarily large numberFor i = 200 To 1 Step -1 ' go in reverse to make spreadsheets order correctly If Trim(Sheet1.Cells(i, 1)) <> "" Then 'only make a sheet if there is a value Set sht = ThisWorkbook.Worksheets.Add 'add the sheet sht.Name = Sheet1.Cells(i, 1) 'rename it End IfNextEnd Sub Link to post Share on other sites
Ignoramus 5,794 Posted August 1, 2013 Author Share Posted August 1, 2013 Copy this into the workbook code module. The sheet with the names should be sheet 1, column 1Sub MakeNewSheets()Dim i As Long, sht As Worksheet'200 is an arbitrarily large numberFor i = 200 To 1 Step -1 ' go in reverse to make spreadsheets order correctly If Trim(Sheet1.Cells(i, 1)) <> "" Then 'only make a sheet if there is a value Set sht = ThisWorkbook.Worksheets.Add 'add the sheet sht.Name = Sheet1.Cells(i, 1) 'rename it End IfNextEnd SubAppreciate the help, but something's not quite right.The workbook has one sheet named Sheet1 and the locations are listed in column A. When I run the macro, there are no new sheets created -- in fact, there's no "feedback" at all. I ran some other macros just to make sure I wasn't screwing something up there and they run. It's Office 2010, if that makes any difference. Link to post Share on other sites
BroadwayG 948 Posted August 1, 2013 Share Posted August 1, 2013 Sub Test() Dim i As Long i = 1 Do Until Trim(Sheets(1).Cells(i, 1)) = "" Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Sheets(1).Cells(i, 1) i = i + 1 LoopEnd Sub Link to post Share on other sites
Ignoramus 5,794 Posted August 1, 2013 Author Share Posted August 1, 2013 Sub Test() Dim i As Long i = 1 Do Until Trim(Sheets(1).Cells(i, 1)) = "" Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Sheets(1).Cells(i, 1) i = i + 1 LoopEnd SubPerfect! Thanks. Link to post Share on other sites
Ignoramus 5,794 Posted August 1, 2013 Author Share Posted August 1, 2013 OK, I've added some to your code, BroadwayG. I'm sure it's ugly as sin, but I've gotten it to do just about everything I need it to do with one exception: my data and sheet names are off by one. Example: Sheet Named 0142 - DENVER is blank Sheet Named 0687 - HOUSTON - has Denver data Sheet Named 1374 - DALLAS - has Houston data Sheet Named 655A - NEW ORLEANS - has Dallas data Sheet Named 8743 - SAN DIEGO - has New Orleans data I'm close but not smart enough to know what I'm missing. Would appreciate some help! Here's what I came up with: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Dim i As Long, branch As String i = 1 Do Until Trim(Sheets(2).Cells(i, 1)) = "" Sheets("Data").Select ActiveSheet.Range("$A$1:$U$9999").AutoFilter Field:=1, Criteria1:= _ branch ' Applies an autofilter to search for the location name ActiveCell.SpecialCells(xlLastCell).Select ' Selects and copies all filtered data Range(Selection, Cells(1)).Select Selection.Copy Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Sheets(2).Cells(i, 1) branch = Sheets(2).Cells(i, 1) Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False i = i + 1 Loop Link to post Share on other sites
Ignoramus 5,794 Posted August 1, 2013 Author Share Posted August 1, 2013 Solved it!I moved the line branch = Sheets(2).Cells(i, 1) up and that took care of it. Link to post Share on other sites
BroadwayG 948 Posted August 1, 2013 Share Posted August 1, 2013 Excellent. Link to post Share on other sites
Recommended Posts
Archived
This topic is now archived and is closed to further replies.