Save copy of workbook with date in specific location and exit

Hello, I am wondering if it would be possible via VBA to save a copy of a daily attendance sheet to a specific shared folder with the date in the file name. So right now, the sheets are named something like this:

010221 Grocery & Dairy 1st Shift ABSENTEE BLANK (1ST SHIFT).xlsm

and and even though this is not the shared drive, this is just a test folder I created which I will change the pathway to the shared one.

C:\Users\bhar05h\Desktop\attendance sheets\1st Shift Grocery&Dairy

Would something like that be possible? To have a fixed file name with a changing date save somewhere specific at the click of a macro-assigned button?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

@PitchNinja,

You end up with a lot of files with the scenario you described. I'd suggest putting a macro-enabled workbook into a specific directory, a subdirectory named Grocery & Dairy or Grocery & Dairy 1st Shift, a file within that subdirectory named Grocery & Dairy 1st Shift ABSENTEE BLANK (1ST SHIFT), and your template should ask the date (with a default of current date), and save when told - to a new sheet with the sheet name being the date, on that workbook in the subdirectory.

In that way, if something hangs you have opportunity for backups to only deal with two files, plus easy additional newly saved logs if you decide to expand the recordkeeping. This way it's also easy to implement without macros just copying over sheets on two workbooks while you edit/write code to make additional features/changes over time...

I find doing most of the structure planning up front helps later on.

Sorry can't help with the coding (just ramping up a new project), but I hope this helps.

GC

Originally Posted by PitchNinja

Also, Grug.Crood, I'm trying to follow what you're saying but I'm not clear. If it helps any, this folder will be named current fiscal year, the live version of this. When the fiscal year is up, the files would be moved to a 21 Fiscal folder or something of the like.

@PitchNinja,

I reckon what I was hinting at is by having a structure hierarchy for each "level" of data you gain a system that can be expanded and easily parsed/accessed.

So this (my suggestion)

(dir) Grocery & Dairy 1st Shift

(subdir) 1st Shift ABSENTEE

main template workbook (can have a shortcut on your desktop...)

[INDENT]

(subdir)Data (where you copy files or add sheets to workbooks)

[/[INDENT][INDENT]

Your workbook with multiple dated sheets

[/[INDENT]

So if my suggestion of trading multiple files (workbooks) for one workbook with multiple dated sheets is not so good because it has 365 sheets, you can just add a subdirectory for, say months and have 28/31 sheets per workbook, or weeks (Julian calendar) with seven sheets per workbook, etc.

It's just how I might organize, everybody is different ... so what I really mean, is just consider how you plan to access things before implementation. For me, I just noticed that directories with hundreds of files can be problematic.

However you decide to organize, I'm betting the coding changes from one method to the next only amount to minor coding changes...

GC

Just making a guess, but would that be something as simple as altering the VBA code from

Yes .. change that portion as you indicated. Keep in mind that anytime you run this macro, the name of the file
will always be the same except for the date.

The following macro saves the sheet as indicated, on my machine :

Please Login or Register  to view this content.

Glad to help ! Cheers.

Originally Posted by Logit

Glad to help ! Cheers.

Actually one more question. I did show the supervision how it works and they do like it, but they are asking me if it is at all possible to have it automatically send the file as an email to a few specific people. Is that something that is doable do you know?

Always the same people ?
Always the entire workbook that is being saved ?

Yes, I believe it is the same 3 or 4 people and it would also be the entire workbook that is emailed. Thinking further on this, sometimes the supervisors do have to email revisions of the attendance sheets out that they've already sent. In light of that, it might be better to even just have it bring up the outlook message with the attachment automatically in there as well as the people to send the email to so that all they'd really need to do is edit the subject line to change the date(unless the date change could also be done automatically in the subject line?) and also edit the body to say whatever they want like "revision to 1st shift sheet" or something along those lines.

Basically, it's a few managers that would want this emailed in case they are out of office and all they have is their phones to check the sheets, at least they could do so from their phones and then make whatever contact with whomever they need to about specific items on the sheet.

If it might even be better/easier, I could propose to them the idea of creating a separate "management email group" (right now there is only one for supervisors, and sometimes managers include themselves in that group and sometimes not) if that would create less of a hassle in the actual coding.

If it IS possible, could that also go inside with the same macro that saves the file?

Hope that clarifies

Give me a little time to review.

.

Please Login or Register  to view this content.

Thank you so much Logit. I didn't get a notification again of your updated post, but happened to check to post another question regarding it here. This updated solution will absolutely work perfectly for what I need! I do have a question regarding the email portion of the code you gave, as I can see that would be handy to be able to just email files too not necessarily save and rename and such.

I tried using it on a file I use regularly and have to email not as often, but I altered it to the specifications of that file and came out with this code:

Please Login or Register  to view this content.

My question is, is there a simple way to alter this code to email just one tab of the workbook like the tab you're currently on or would you have to specify its name? Would it be this specific line of coding that would need changed:

.Attachments.Add Application.ActiveWorkbook.FullName

or would it be more involved than that?

Thanks again so very much for your help!

.
This modified macro will attach the ACTIVE SHEET and email.

Please Login or Register  to view this content.

I've altered the coding as needed to fit very specific needs and it is working as I want it to. These all will surely come in handy. Thank you very much again for all your help in this!

Glad to help.

PitchNinja

There is a difference between a desktop computer and accessing Net Work locations. Regretfully I am not familiar with the Net Work environment.

Would someone else on the Forum please assist PitchNinja with this need ?




(ps: PitchNinja ... you may need to mark this thread SOLVED. Then repost with the final macro in your post (reference back to this URL as well in your message) requesting assistance with
the Net Work Environment. Suggest a title for your new post similar to : Macro Works On Desktop But Not On Net Work Environment. I will attempt to follow your progress as I am
interested in learning the differences.)

No worries, thank you for all your help, it is at least usable for the files I DO have on desktop. I will create a new thread.

On my desktop computer, the line you are using works here :

Please Login or Register  to view this content.

If it isn't working there, I'm guessing it is due to the network environment, of which I am not familiar.

Originally Posted by Logit

On my desktop computer, the line you are using works here :

Please Login or Register  to view this content.

If it isn't working there, I'm guessing it is due to the network environment, of which I am not familiar.

Is that the email portion though? That was working fine for me - It was the Saving to location issue. I was actually finding that even on the desktop it was only saving the one tab and not the whole workbook.

I misunderstood ...

Try this resource : https://docs.microsoft.com/en-us/off...-network-drive

Originally Posted by Logit

.

Please Login or Register  to view this content.

If we go back to this post which is the one you have for saving the file to the desktop, is it something in this portion of the code that would save all tabs and not just one:

Please Login or Register  to view this content.

Would it be a small change to the code? I tried doing something like this with the underlined being what I changed:

Please Login or Register  to view this content.

That's ultimately what I'm looking to change, not save just the one tab, but the whole file. If I can get it to work on the desktop test copy, I should be able to get it to work on the network. I'm just not sure what the coding change is from Sheet to workbook. I hope that might clarify?

You shouldn't need the "ActiveWorkbook.Copy" ... nor

Please Login or Register  to view this content.

Try it without those items

EDIT: I found it. I had to add "ActiveWorkbook.Save" in place of where I had ActiveWorkbook.copy.

Thank you once again for steering me on that, Logit.

Glad you worked it out.

Cheers.