Macro to open sheets, rename sheets as number plus event name and then close with save as new name

[drhatmrexcel]

I am using Excel 2007 and Windows Vista.

I am using a set of 40 spreadsheets for tracking sporting events and sorting out the results and then the winners name and the number of competitors in each event is linked to a master tally sheet for review. I also use the master tally sheet to manually enter/input each event name.

Currently I input the names of the events (up to 40) in cells B6 through B45 of the master tally sheet and then I open the individual event sheets which are pre-named as 001) .xlsm through 040) .xlsm, resave them as 001) Event name in cell B6.xlsm thru as many events as will be held and are contained in column B. This then updates the links in master tally sheet to the new file name sheet. I then manually delete the files 001) .xlsm through up to 040) .xlsm as many files as I have opened.



Sub rename()
'
' rename Macro
'

'
Workbooks.Open Filename:= _ "C:\Data\Event\\001) .xlsm"
Windows("Master Tally Sheet.xlsm").Activate
Application.Goto Reference:="R6C2"
ActiveCell.FormulaR1C1 = "Class 1"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Kristen ITC"
.FontStyle = "Bold"
.Size = 48
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Windows("001) .xlsm").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:\Data\Event\ 001) Event 1 name.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Close
End Sub

This above macro cannot be reused because it would always name the file as Event 1 name instead of the actual contents of cell B6 and downward through B45 when I would get it. So the macro has to be able to get the cell contents from column B instead of my code which was done with a simple copy and paste.

So any pointers would be appreciated as well as a possible way to check and see how many cells in column B, cells B6 through B45 actually contain an event name and then only cycle through the “save as” code as many times as necessary to complete the save as procedure and also complete the same number of corresponding file deletes.

One other possibility would be to have a single common boiler plate file that would be opened, saved as, and then reopened as many times as necessary to have enough event sheets.

Pulling Order[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[TH]G
[/TH]
[/TR]
[TR]
[TH]5
[/TH]
[TD="align: center"]No.
[/TD]
[TD="align: center"]Event Name
[/TD]
[TD]Trophy Sponsors
[/TD]
[TD]Winner First Name
[/TD]
[TD]Winner Last Name
[/TD]
[TD]Count - Number of contestantss entered
[/TD]
[/TR]
[TR]
[TH]6
[/TH]
[TD="align: center"]1
[/TD]
[TD]Class 1
[/TD]
[TD]Lets see who can sponsor this class
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7
[/TH]
[TD="align: center"]2
[/TD]
[TD]Class 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8
[/TH]
[TD="align: center"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9
[/TH]
[TD="align: center"]4
[/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10
[/TH]
[TD="align: center"]5
[/TD]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]11
[/TH]
[TD="align: center"]6
[/TD]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]12
[/TH]
[TD="align: center"]7
[/TD]
[TD="align: right"]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]13
[/TH]
[TD="align: center"]8
[/TD]
[TD="align: right"]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]14
[/TH]
[TD="align: center"]9
[/TD]
[TD="align: right"]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

Cell	Formula
E6	='C:\Event\[001) Class 1.xlsm]1'!$D$7
F6	='C:\Event\[001) Class 1.xlsm]1'!$E$7
E7	='C:\Event\[002) .xlsm]1'!$D$7
F7	='C:\Event\[002) .xlsm]1'!$E$7
E8	='C:\Event\[003) .xlsm]1'!$D$7
F8	='C:\Event\[003) .xlsm]1'!$E$7
E9	='C:\Event\[004) .xlsm]1'!$D$7
F9	='C:\Event\[004) .xlsm]1'!$E$7
E10	='C:\Event\[005) .xlsm]1'!$D$7
F10	='C:\Event\[005) .xlsm]1'!$E$7
E11	='C:\Event\[006) .xlsm]1'!$D$7
F11	='C:\Event\[006) .xlsm]1'!$E$7
E12	='C:\Event\[007) .xlsm]1'!$D$7
F12	='C:\Event\[007) .xlsm]1'!$E$7
E13	='C:\Event\[008) .xlsm]1'!$D$7
F13	='C:\Event\[008) .xlsm]1'!$E$7
E14	='C:\Event\[009) .xlsm]1'!$D$7
F14	='C:\Event\[009) .xlsm]1'!$E$7
G7	=IF(MAX('C:\Event\[002) .xlsm]1'!$B$7:$B$206)=0,"",(MAX('C:\Event\[002) .xlsm]1'!$B$7:$B$206)))
G8	=IF(MAX('C:\Event\[003) .xlsm]1'!$B$7:$B$206)=0,"",(MAX('C:\Event\[003) .xlsm]1'!$B$7:$B$206)))
G9	=IF(MAX('C:\Event\[004) .xlsm]1'!$B$7:$B$206)=0,"",(MAX('C:\Event\[004) .xlsm]1'!$B$7:$B$206)))
G10	=IF(MAX('C:\Event\[005) .xlsm]1'!$B$7:$B$206)=0,"",(MAX('C:\Event\[005) .xlsm]1'!$B$7:$B$206)))
G11	=IF(MAX('C:\Event\[006) .xlsm]1'!$B$7:$B$206)=0,"",(MAX('C:\Event\[006) .xlsm]1'!$B$7:$B$206)))
G12	=IF(MAX('C:\Event\[007) .xlsm]1'!$B$7:$B$206)=0,"",(MAX('C:\Event\[007) .xlsm]1'!$B$7:$B$206)))
G13	=IF(MAX('C:\Event\[008) .xlsm]1'!$B$7:$B$206)=0,"",(MAX('C:\Event\[008) .xlsm]1'!$B$7:$B$206)))
G14	=IF(MAX('C:\Event\[009) .xlsm]1'!$B$7:$B$206)=0,"",(MAX('C:\Event\[009) .xlsm]1'!$B$7:$B$206)))

<tbody>
[TD="bgcolor: #FFFFFF"]
Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]




Thank You,

Dennis

 

[AlphaFrog]

Try something like this. Substitute your BoilerPlate Path and file name and your Save Path.

[COLOR=darkblue]Sub[/COLOR] rename()


    [COLOR=darkblue]Dim[/COLOR] wbBP [COLOR=darkblue]As[/COLOR] Workbook, rngB [COLOR=darkblue]As[/COLOR] Range, cell [COLOR=darkblue]As[/COLOR] Range, strPathFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=green]'Range of File names[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rngB = ActiveSheet.Range("B6:B45").SpecialCells(xlCellTypeConstants)
    
    [COLOR=green]'File of Boiler Plate workbook[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wbBP = Workbooks.Open(Filename:="[COLOR=#ff0000]C:\Data\Event\BoilerPlate.xlsm[/COLOR]")
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] rngB
        strPathFileName = "[COLOR=#FF0000]C:\Data\Event\[/COLOR]" & cell.Value & ".xlsm"  [COLOR=green]'New workbook path and file name[/COLOR]
        wbBP.SaveCopyAs Filename:=strPathFileName
        [COLOR=green]'Hyperlink[/COLOR]
        cell.Parent.Hyperlinks.Add Anchor:=cell, _
                                   Address:=strPathFileName, _
                                   ScreenTip:="", _
                                   TextToDisplay:=cell.Value


    [COLOR=darkblue]Next[/COLOR] cell
    
    wbBP.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[drhatmrexcel]

Well this did not work as once again there are so many variables in the question that the answer is not entirely that easy to get right the first try. So try, try again I must.

I have a boilerplate directory/folder that I have various spreadsheet files in that I use for tracking sporting events.
The boilerplate directory/folder I copy to create a new set for every different day(s) when events are held.
On one spreadsheet in the boilerplate directory I have one spreadsheet file called “master tally sheet”. On it I enter the names of up to 40 different classes to be held during the days sporting event. I enter these class names in cells B6 through B45 of the “master tally sheet” file.

Also in the boilerplate directory/folder I have 40 files which although they are all based on the exact same boilerplate file, I have them all pre-named “001) .xlsm”,”002) .xlsm” through “040) .xlsm” respectively. These 40 sheets are where I enter the information from the up to 40 classes that may occur during a day’s particular sporting event.

Typically I manually open and then individually rename as many of the the 001) .xlsm through 040) .xlsm files as necessary to match what classes are needed for the particular event that I am tracking. I open and rename these 40 files based on the class names that are in cells B6 through B45 of the “master tally sheet” file. First I would go to cell B6 and copy the contents of B6 to the clipboard and then I would open “001) .xlsm” and resave it with a new name by doing a file save as “001) class 1 name.xlsm” where “class 1 name” is whatever was the content of the clipboard from cell B6 of the master tally sheet. I would then close the newly renamed and resaved file and delete the “001) .xlsm” file.

I would repeat for files “002) .xlsm” but use the next cell down B7 contents during the save as process. I would then proceed through the rest of the files down through as many rows of column B of the master tally sheet based on the number of classes that were scheduled to be held as part of the day’s sporting event.

What I am trying to do is write a macro in the “master tally sheet” that will automate my manual actions.

The macro that I originally posted was always looking to open a single file called BoilerPlate.xlsm which was always in the specific directory of C:\Data\Event but in reality the directory location is subject to change and the file to be opened is not just a single file called BoilerPlate.xlsm but would be the previously mentioned series of files of “001) .xlsm” through up to “040) .xlsm”.


Because I have links in the “master tally sheet” to each of the “001) .xlsm” through up to “040) .xlsm” to update the master tally sheet with the individual class winners I cannot simply rename the “001) .xlsm” through up to “040) .xlsm” files, I have to open them and resave them so that the links remain valid.

Of course there is an alternative that I can think of which would be a different tact and would require a macro to identify the number of cells in column B that have class names, open a single boiler plate file, do a save as of the boiler plate file as a new file name and then insert links in the master tally sheet to the newly created files in the proper row for that class. But I think that may be more cumbersome to execute.

And yes I do realize that the contents of cells in column B cannot contain any illegal file name characters which would cause a failure of the macro to do a file save or save as action.

So any direction on code for a macro for in the master tally sheet would be welcome.

Thank You,
Dennis