Macro to populate cell A1 with a storeID and save file to a specified folder & repeat from a list of 100 stores

[justmeok]

Hi everyone not sure if this is even possible?

I have a list of 100 store id's (in an excel file) and I have an excel template workbook that I need to have one workbook for each store id. I would like the macro to populate cell A1 with the first store id on the list then save to a specified folder using the store id as the filename with the current year on the end. eg STOREID1 2016.xlsx. The macro needs to repeat that action for every store id on the list.

The solution doesn't have to be a macro I am happy to do whatever will work rather than having to do it all manually.

I currently use Excel 2016 on a pc

Thanks everyone

 

[Perpa]

justmeok,

Assuming you have the workbook opened with the "STOREID" numbers and you are looking at the list of numbers in column A, the following code will add a new workbook for each "STOREID" and save it as an 'xlsx' filetype in the same directory as the original workbook.
Perpa

Sub Test()
Dim rw, LastRow As Long
Dim wbStart, wb As Workbook
On Error GoTo ErrHandler
Application.ScreenUpdating = False
wbStart = ThisWorkbook.Path & "\" & ThisWorkbook.Name
LastRow = Range("A" & Rows.Count).End(xlUp).Row     'Assumining your list is in column A, sheet1 of the open workbook
For rw = 2 To LastRow
    If Cells(rw, "A") = "" Then GoTo PASSEM   'Skips any blank cells in STOREID column of STOREID numbers
    nm = ThisWorkbook.Path & "\" & "STOREID " & Sheet1.Cells(rw, "A") & " 2016"
 
    Set wb = Workbooks.Add
    wb.Sheets(1).Range("a1").Value = "STOREID " & Sheet1.Cells(rw, "A")
    wb.SaveAs Filename:=nm & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    SetAttr nm & ".xlsx", vbNormal
    wb.Close True
PASSEM:
Next rw

Application.ScreenUpdating = True
Msgbox ("Workbooks Created")
Exit Sub

ErrHandler:
Application.ScreenUpdating = True
MsgBox "Error number - " & Err.Number & vbCrLf & "Error description - " & Err.Description
End Sub

 

[justmeok]

Thank you Perpa but I think I must be doing something wrong! When I run the macro it just puts STOREID in cell A1 and saves the workbook into C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART and after the first one it just keeps doing the same thing and asking if I want to save over the workbook already there. It does not actually put the different STOREIDs in the subsequent files but just keeps putting STOREID in rather than one from the actual list in column A. Also it just saves a blank workbook how do I get it to save the template with the new name/s? The workbook with the list if STOREID's is not in the path above but is in a folder within the my documents folder so not sure why it is saving to the path above?
Thank you very much for trying to help me

 

[Perpa]

justmeok,

From what you described, you have copied the code to your Personal workbook, not to a new workbook in the
directory you specify...something like 'C:\Users\username\MyDocuments\STOREIDs'
That is why you got the directory 'C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART'.
You probably want to delete any 'STOREID ##.xlsx' files from the '...\XLSTART' directory.
Make sure you don't delete the file named 'Personal.xlsb'.

You need to load the template into a new workbook, and then save the new workbook with a new name, ie. 'Master STOREID' as a macro enabled file, to the appropriate directory in 'MyDocuments'. Once you have the 'Master' stored into your directory we can move forward. This will allow you to copy the code into this new workbook, not the into the startup directory as you did earlier.

The code I provided earlier did not copy the template, just the STOREID # to a new workbook, sheet1, cell A1. We need to revise the code to also copy the sheet with the template into those new workbooks.

So we will need some additional information to revise the code I provided earlier:
What is the name of this new workbook with the template you want to copy?
What is the name of the sheet (and sheet number) that you copied the template to?
This information will get us headed in the right direction.
Let me know how that goes.
Perpa

 

[justmeok]

Thanks Perpa I'm sorry I didn't make it clear in my original post that I need a template to be copied and re-named for each store. And yes you are totally correct in that I did save it to my personal workbook . I now understand where I went wrong! Following are the answers you need to get this underway for me.
What is the name of this new workbook with the template you want to copy? QA & C Master
What is the name of the sheet (and sheet number) that you copied the template to? Summary (Sheet 1) and Monthly Compliance (Sheet 2)
I have now finished the final design and so the STOREID needs to be copied into cell C1 on the Summary (Sheet 1). Both sheets in the template have formulas but all formulas refer to data within the master workbook template. The name of the workbook that has the list of STOREIDs is called Store Listing and the list is on sheet 1. There is no header row in the list and so it starts at A1 and finishes at A100. Is this ok being in a separate workbook or do I need to include it in the template? Not ideal for it to be in the template unless you can prevent that from being copied when the new workbooks are created? I hope I have explained this clearly and thank you again for helping me, I am very grateful

 

[Perpa]

justmeok,

Sorry I haven't gotten back to you, but I have been a little busy with holiday activities and my volunteer work.
I won't have much time to look at this for a few days as I will be travelling to see my grandkids (tis the season!).
But a couple more bits of information are required:

Where will you be saving the new workbook files? In the same directory with the file QA&C, or in a separate folder?
What is the name of the folder where you store the QA&C file, and what is the name of the folder where you will store the new STOREID files?
Can the 'QA&C' file and the 'Store List' file be located in the same folder? If not, then what is the folder for the 'Store List' file?

I'll get back to you when I have a moment to respond.
Perpa

 

[Perpa]

justmeok,
Since I will be travelling the next few days, I did a search of this forum with the search phrase 'make copies of workbook' and came up with one that may be exactly what you need; see Post #4 thereof:

http://www.mrexcel.com/forum/excel-questions/327436-create-multiple-copies-workbook.html

I will check back when I get some time. Good Luck!
Perpa

 

[Perpa]

justmeok,
I had a little time this morning so I created a folder 'QA&C'. I put the 'Store List' and template file 'QA&C_MASTER' and a new folder, 'StoreIDs', into that folder. I copied the following code into a standard code module in the file 'Store List'. Notice I didn't use the function 'GetDirectory' because I already knew the folder I wanted the new files stored into...'StoreIDs'. Change the 'your Path' in the 3 locations shown to suit your file structure.
This worked like a charm! Many thanks to Trebor76 for this code!
Perpa

Sub Test2()
Dim strSourceFile, strDirLocation, strCopyName As String
Dim objFSO As Object
strSourceFile = "C:\[COLOR=#ff0000]your Path[/COLOR]\QA&C\QA&C_MASTER.xlsm"    'Template location, Change 'your Path' to suit
 strDirLocation = "C:\[COLOR=#ff0000]your Path[/COLOR]\QA&C\StoreIDs\STOREID "     'Change 'your Path' to suit, notice the space at the end...that was intentional, removed if not needed
 
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
        For Each rngCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
            strCopyName = rngCell.Value & ".xlsm"
            objFSO.CopyFile strSourceFile, strDirLocation & "" & strCopyName, overwritexisting = True
        Next
    MsgBox "STOREIDs copied to the directory:" & Chr(10) & Chr(10) & "C:\[COLOR=#ff0000]your Path[/COLOR]\QA&C\StoreIDs"     'Change 'your Path' to suit
End Sub

 

[justmeok]

Oh Perpa thank you so very much! You are a lifesaver and this worked a treat I looked at the link you provided but I got a bit bogged down with the UDF and did a bit of googling but I was not at all confident that I fully understood how to do it. Your solution worked so well and was easy for me to understand and implement! I very much appreciate you giving up your time at this hectic time of the year to help someone on the other side of the world! Enjoy your xmas with your family and I hope 2017 is a wonderful year for you! Thanks again for your help

 

[Perpa]

Oh Perpa thank you so very much! You are a lifesaver and this worked a treat I looked at the link you provided but I got a bit bogged down with the UDF and did a bit of googling but I was not at all confident that I fully understood how to do it. Your solution worked so well and was easy for me to understand and implement! I very much appreciate you giving up your time at this hectic time of the year to help someone on the other side of the world! Enjoy your xmas with your family and I hope 2017 is a wonderful year for you! Thanks again for your help

justmeok,
Very good news indeed. You are welcome. Am enjoying the holidays with family in the Pacific Northwest. Happy holidays!
Perpa