Looking for a code that will save as different names to folder based on data validation

Hello @PitchNinja. If I understand you correctly, then here is an option for you.

Please Login or Register  to view this content.

Good Luck!

Hi Mike, thanks for responding. So I went to run the procedure and I got a path not found run-time error. When i hit debug, it highlights the line that says MKDir folderpath. Is there a reference I maybe need to add? I have the following checked:
*Visual Basic For Applications
*Microsoft Excel 16.0 Object Library
*OLE Automation
*Microsoft Office 16.0 Object Library

It's just a wild guess but I wasn't sure if maybe there was something I needed to enable there that was causing the run-time error.

I tried to reproduce your error and my code worked fine, without errors. Despite the fact that I deleted the folder on the desktop, it was created again without problems, without a single error. Are you by any chance trying to create this folder from this file that is in the public domain? Or are you logged in not as an Administrator but as a regular Windows user? If yes, then you cannot create this folder when working as a regular Windows user!

I am not logged in as admin. I sign on to this computer here at work everyday, but as far as I am aware, it is not as admin.

Okay I think it's maybe a different code entirely then. I apologize. When I had said "So what I'd like to do is have a macro that will select each of these department codes and then save a copy of the file in a folder on the desktop called "Sample",
I thought maybe it would have been understood from that sentence that I wanted multiple files saved which was why I thought some kind of loop was needed. But yeah that's what I am looking for. In this case because there are 4 different departments - Test1, Test2, Test3, and Test4 - I wanted the macro to select them one at a time and save each as it's own individual copy in the sample folder. So by that, we would have the following 4 files in the Sample Folder: "Test1 - 1st Shift", "Test2 - 1st Shift Office", "Test3 - 2nd Shift", and "Test4 - 3rd Shift". Hope that was clearer.

If I understand you correctly

Please Login or Register  to view this content.

Good luck!

Thanks Mike, I tested this out on my home computer with Excel 2007 and changed the D:\Sample to C:\Sample and it worked. It did exactly what I was expecting - it created 4 files. I even added two more sample departments to test them and sure enough it created a total of 6 files. I will be out of the office next week from work so I won't get to test this on the computer there until I return the following week but it IS working on my Home computer, so fingers crossed that it will work when I get back. This is amazing though that this is possible - I figured there had to be some kind of loop in there and had "For Each" in my head but all the other added lines....yeah I got a WAYS to go there. I can't even imagine how long it took to come up with coding like this, so thank you very much and EXCELLENT work! We'll see when I get back how it goes

It didn’t take much time, we had our own things to do. Regarding your work computer, you will not be able to create a folder on the local drive C. This is a security policy, system administrators limit the actions of ordinary users. Otherwise there would be a complete mess on work computers. As an option, just create a folder on local drive D, which is what I recommended to you before. Well, or contact your system administrators. Good luck.

Okay, I finally got caught up with my other stuff from being out of the office so that I could try this out. So it works in that it is saving files to the preferred location and it did save the files as the correct names, so that is very awesome! Looking closer however, I realized it is copying Sheet1, so let me try to clarify what I was after. I apologize as I see now how I could have communicated it better. It is ultimately Sheet2 that I need copied and saved to the location after each selection from the dropdown menu is selected on that sheet. Sheet1 basically serves as the lookup for Sheet2's data and wouldn't be copied/saved to a new workbook. Sheet 2 would basically be a personalized template for each department that gets printed out after it is saved under its different names. So in a step by step scenario with this specific sample, here is what I would hope to have after running the macro:

On Sheet2
1. Select the first dept. from dropdown (Test1) - Names are populated for people who belong to that dept.
2. Copy Sheet2 - Save as new Workbook
3. Save new workbook in specified location as the specified name
4. Select the next dept. from the dropdown (Test2) - Names are populated for people who belong to that dept.
5. Copy Sheet2 - Save as new Workbook
6. Save new workbook in specified location as the specified name
7. Select the next dept. from the dropdown (Test3) - Names are populated for people who belong to that dept.
8. Copy Sheet2 - Save as new Workbook
9. Save new workbook in specified location as the specified name
10. Select the next dept. from dropdown (Test4) - Names are populated for people who belong to that dept.
11. Copy Sheet2 - Save as new Workbook
12. Save new workbook in specified location as the specified name

Sheet2 is saved as 4 different files and if you open each one up, they would all of course look different from each other. We are close to the answer and I think maybe the worst of it - the saving to location - is out of the way and at least that is working. Just the Sheet2 portion. Again, I do apologize for not having communicated it better initially but hope that this is clearer and still doable? Thank you for the time you spent on this.

Hello @PitchNinja. To be honest, I didn’t understand what you needed. In fact, the code works as you described in your post.

Have you tried using the code from post #11? That's how it works.

Just thinking some. I don't know how VBA handles selecting from dropdown menus, but if it would make the code any simpler, the code could simply copy the department codes from column L of Sheet1 and paste each into B8 on Sheet2 since the selections in the drop-down menu there are coming from that column anyway. So in this case, copy Test1 from Column L in Sheet1, paste into B8 of Sheet2, then save sheet 2 as its specified name for Test1 in the specified location, then copy Test2 from Sheet1, paste into B8 of Sheet2, save Sheet 2 under its specified name for Test2 in the specified location and then so on and so forth.

Hello. Make an example file based on that file and add another sheet (this sheet will be similar to the new file in the final result) as you see the solution to your question. So far there is a lot of text from you but nothing is clear. Sorry.

Your code is only doing about half of what I am after. It's saving files under correct names and it is saving to the correct location. The problem is that it is not saving the correct Tab as a new file. It's saving Sheet1 as a new file, it needs to save Sheet2 as a new file. I listed a numbered step-by-step process before and I will do that again but with the method of copying and pasting between Sheet1 and Sheet2. In the same file I attached earlier:

Step 1. On Sheet1, Copy the first department code from Column L(So starting in L2, this is "Test1")
Step 2. Go to Sheet2, paste that into B8 - names and numbers will populate on Sheet2 starting in row 11 and on for anyone who belongs in the department pasted in B8
Step 3. Save Sheet2's name as what appears in cell B8, then a dash, and then what appears in cell B7 in the sample folder. So for the first one, the filename would be "Test1 - 1st Shift"

Repeat this 3 step process for however many departments are listed in Column L of Sheet1. In this sample we only have 4 departments, but there would be more in the actual file. Opening any of these files will have the tab with data as it appears on Sheet2.

I really can't make this any clearer what this macro should do and this really isn't a lot of text.

Hi @PitchNinja. For a long time I could not understand you, apparently life circumstances still influence a person. Sorry, I think this is what you need in the end result.

Please Login or Register  to view this content.

Good luck.

Thank you Mike, THIS is what I was looking for Although question, could we have this code not do whatever auto-fitting/resizing of columns that it is doing in the new sheets?

Hi. You can remove or comment out this line:

Please Login or Register  to view this content.

Awesome thank you very much for your help on this! I hope the life circumstances will start to settle for you soon

Thank you @PitchNinja. I would like all this in my country to end as soon as possible. Good luck to you.