Master Shipping Schedule Drawing From Separate Files

[Matthew Berg]

Looking for some guidance on a VBC Code / Macro.

I have a master spreadsheet for shipping managed by the Operations Manager. The Project Managers provide shipping dates, and the O.M generates a master shipping schedule for the week of "x".

My hope is to have each person (OM, PM1, PM2, PM3 etc) maintain their own spreadsheet as a separate file, stored in a common location. The OM would then open the MASTER file and work from there, but for simplicity I've included it all in one spreadsheet.
And here's my attempt at an explaination

I've included 4 versions of what I want the 'MASTER' spreadsheet to look like. Blank, and three different versions as dictated by a date range (C1 to E1). 'MASTER 1' has searched all ROWS on 'PM1', 'PM2', 'PM3' (etc) and returned the values in those ROWS as dictated by the date range. 'MASTER 2' has returned different values again based upon the date range, ditto for 'MASTER 3'.

My end hope is that the OM can dictate that all PM's (i've left it at 3 for now, but there are more) update their Shipping Schedule (simply called PM1, PM2 for now, but as previously stated, would be a separate file) by 12:00 on Tuesday, and then he can open his MASTER file, input his desired date range, run the Macro, and then produce an updated shipping schedule; I suppose the values would still be there from the previous week as it's the same file, but then replaced once the macro is run. I would like to keep the sort option if possible so that he can view based up on the values in the columns (date, project #, PM etc).

I've found threads that appear similar to what I'm looking for, but I don't know enough about VBC yet, so hence the request for guidance.

Many thanks

 

[Nebu]

Hope this is what you are looking for. See the attached. It will add data from PM tab to Master tab based on the date criteria. The data in the Master tabs will be added on a cumulative basis, you will have to delete the data from Master sheets if you do not wish to keep previous data. This can be incorporated into the code if this is what you are looking for.

Thanks

 

[Matthew Berg]

Nebu, thanks so much this is very very close to what I'm looking for. I like the 'Consolidate' button. You hit it on the head about deleting the data; would it be easiest to have a 'Clear' Button? As well:

• Can this work across files; as if PM1, PM2, and PM3 were there own workbooks?
• How can I add PM4, PM5 etc as they occur?
• Formatting will be my next step....what should I avoid doing as I clean it up? Borders, graphic etc.....

Thanks again

 

[Matthew Berg]

Okay, so I've worked with this a little bit.
I now have a file that can I can open, hit 'clear', enter a new date, 'consolidate', sort by date, then email. This is so close to what I want, but this is currently setup to work with the tabs within the file. Is there a way to separate the tabs to new files? What would the file management need to be? I can open the VBA code and have started to see how it all works, but I'm still not to a point where I can set it up. Particularly to the point where I would need to add a new PM to the mix, but I would think I could just copy & paste the code and alter the values? Thanks again!

 

[Nebu]

Hi:

I have modified your macro and have made it "one-click". I have removed a lot of unnecessary coding, and the macro I wrote is flexible enough to accommodate "n" no of PM sheets. It basically loops through all the worksheets in the workbook and if the worksheet name is <> Master it will check for the date range as per the Master sheet and copy the respective rows and paste it in the Master sheet. Please find the attached and let me know with questions if any......

 

[Matthew Berg]

I'm getting an error code when I press Consolidate. It worked the first time and it generated the email, but now I just get 'System Error &H80071779 (-2147018887).

As such I didn't get much of a chance to review I'm not sure if all of my buttons were incorporated. To be honest I think it would be preferable to have the 4-Step buttons, as the fellow who would be using this would want to see as much of the action as possible.

I'm still uncertain if this can be done with separate files? Move all of the 'PM' sheets to their own file and have them feed the 'MASTER' file?

 

[Nebu]

Hi:

Which part of the code is giving you the error? Yes the macro can be done in steps , no problem the code will remain more or less the same. Regarding creating a separate file for PM yes it is possible but the code has to be tweaked to bring in this functionality. Let me know your thoughts....

 

[Matthew Berg]

I'm not certain which part was giving me the error. It works the first time that I press it, and it opens the PDF and opens email. When I press it again, it gets the error, but if I close the PDF & email, it works again. As I do this repeatedly I do get an error sometimes, but I can't see a pattern.

It would be great to have it in steps again, and to have it as separate files. One thing I'd added that I liked was the clear button made the input dates 'Red' and the 'Email' changed them to 'black' for the send-out. Ultimately, this sheet will be used by someone who I'm not certain on their tech-savvy...so to have a colour coded chart seems easy to manage, but I know he'd like to see the steps as well and not blindly trust that a one-click does it all. Would there be certain parameters to the files, ie: they'd have to be in the same directory as the master file? Would that be able to change on the code if/as it moves around on the server? Difficult to add new PM's?
I'm asking because as I mentioned, I'm passing this file off once you've mastered it

Is there a VBR directory of commands? I've learned lots by reading the code, but there are still some functions/commands I can't follow.

Many thanks again!

 

[Nebu]

Hi:

I have fixed the error in your macro, it had something to do with the code for sending email. I have put the code assuming that you are using Outlook as your email, remember to check your outlook library before running the macro go to VBA editor -> tools-> references and search for outlook library in the list and check the check box.

I have also moved all the PM tab to a different file and if you save both your Master file and PM file in the same folder the macro should work fine . Let me know with questions if any....

 

[Matthew Berg]

I've opened the Master File, enabled macro, clear works, but when I press Consolidated, the VBR Editor is opened and I receive the following error
"Compile Error: Can't Find Project Or Library". The word 'fasle' is highlighted too. Is this supposed to say 'false'? I tried changing it, save and re-run, but I get the same error and opening of VBR editor, however this time it highlight "i" in the "For i = 3 To ws.UsedRange.Rows.Count" code-line. I don't understand what this 'i' refers to so I don't want to start messing with it.

To add additional PM's say a year from now, would I use

Workbooks.Open Filename:=ThisWorkbook.Path & "\PM File.xls"
Workbooks("PM File.xls").Activate

and change the title for each PM workbook I want to feed the Master file?

For example:

Workbooks.Open Filename:=ThisWorkbook.Path & "\PM1.xls"
Workbooks("PM File.xls").Activate
Workbooks.Open Filename:=ThisWorkbook.Path & "\PM2.xls"
Workbooks("PM File.xls").Activate
Workbooks.Open Filename:=ThisWorkbook.Path & "\PM3.xls"
Workbooks("PM File.xls").Activate

I feel this is so close to what I'm looking for, and I thank-you.

Would I be able to rename the files and if so will I have to search and replace all references in the VBR?

Again, much thanks I'm definitely going to look into some further learning on VBR

 

[Nebu]

Hi:

I have fixed the macro

• Error with "i"- it was my bad that I used "Usedrange" to count the rows, "Usedrange" may count cells that do not have any data if the cells were not cleaned up properly.

• Error with false - it was a simple typo error the spelling of "false" was wrong

• As far as creating multiple files it can be done , but I would recommend you to keep all the PM details in one workbook in different tabs else you will have to open every PM file if you have to retrieve the info. Furthermore, if you add more PM files we need to add codes to count the no.of PM files in a particular folder to loop through all the files and retrieve the information.

Let me know with questions if any....

 

[Matthew Berg]

Good Morning Again!

I'm still getting an error with the 'i'.

Maybe I'm not doing it right.....do both files have to be open? Macros turned on in both? Do they have to be in the same folder? I read the code "Workbooks.Open Filename:=ThisWorkbook.Path & "\PM File.xls" and think does that do down a folder? Does the .xls have to appear in the filename? Also, I see "MASTER" in the code and the filename is "Master - Nov 14 no logo"....will that make a difference? Would I have to change that part of the code if I were to rename it?

As far as multiple files, I'm sure that will be the preferred method, though perhaps tedious at first. Ultimately I'm trying to shave down some time for my boss.....this takes him approx 2 hours at this point per week, in addition to the 1/2 hr each PM takes to update their schedule. If it can be whittled down to 5 mins, then that's a huge saving.

Many Thanks!

 

[Nebu]

I am not sure why you are getting an error at your end, the macro is running fine at my end , could you please upload the file you are trying to run the macro from.

• ThisWorkbook.Path & "\PM File.xls - ThisWorkbook.Path will be the file path where your macro file is saved. Note: Both the PM files and the macro file has to be saved in the same location.
• MASTER- is a tab in your macro file.
• Master - Nov 14 no logo is your macro file.
  
• i- is the count of rows in each tab in the PM file it is dynamic it will change per tab. For eg: If tab 1 in the PM file is having 3 rows then the value of i will be 3 and if the tab 2 in the PM file is having 7 rows then the value of i will be 7.

you have to activate the macro only in the MASTER file. There are no macros in the PM File. The macro basically opens the PM File go through all the tabs and filter for the date criteria as given in the Master - Nov 14 no logo file and retrieve the data.

If you can share the file I can go through what you have done and can tweak the code for multiple files.

Save the attached files in the same location the macro should work.

Thanks.

 

[Matthew Berg]

Well I'm at a loss. The error is still popping up. I've opened both files, they're in the same directory. I've even been spending a lot of time reading about VBA so some of this is actually making sense now. But I don't get it. I've attached a screenshot of my error as well, hope that helps. I've also dated the files to keep track. Thanks for sticking this out!

 

[Nebu]

In the consolidated macro instead of
Dim c As Long make it Dim c, i As Long
I forgot to define i strangely in my machine it was working with out giving an error so i over looked it.
Also the file name in the macro is PM file do not change the file name it will give an error

 

[Matthew Berg]

Oh wow, it works! The Email button now appears buggy....
If I change the names / add additional PM files, do I just need to alter the code
ie:

Workbooks.Open Filename:=ThisWorkbook.Path & "\Jeff Shipping.xls"
Workbooks("PM File.xls").Activate
Workbooks.Open Filename:=ThisWorkbook.Path & "\Matthew Shipping.xls"
Workbooks("PM File.xls").Activate
Workbooks.Open Filename:=ThisWorkbook.Path & "\Sam Shipping.xls"
Workbooks("PM File.xls").Activate

Does the email work based upon the computer user? So if I drop this file on someone else's computer will it work for them?

Thanks!

 

[Nebu]

No, You cannot alter the codes like that, it has to be written in a different way. Regarding the email error , did you activate the outlook library as I had mentioned in the post #9 if no, please follow the steps as I had mentioned there to activate the library and let me know. I will be really tied up in a training for next couple of days , but will try to work on your macro if I get time.

Thanks

 

[Matthew Berg]

No worries if you need some time off, I think you've earned it! I'll be picking up a VBA book anyways so maybe I'll noodle on my own. I looked back at Post #9 and reviewed my references. I've attached a screen shot because I can't seem to find the noted 'Missing Microsoft Outlook 14.0', I can only see 12.0

Hope that the code rewrite for different workbooks isn't too cumbersome, I await your expertise.

Many regards,

 

[Nebu]

Which version of outlook are you useing let me know the macro has to be modified accordingly....

 

[Matthew Berg]

2007 - (12.0.6691.5000) SP3 MSO (12.0.6683.5000)

 

[Nebu]

Instead of Dim olApp As Outlook.Application, try putting Dim olApp As Object, it may work. I cannot test it at my end as I am using outlook 2010.

Thanks
Nebu

 

[Matthew Berg]

That's not working, however I believe I can make a macro to do the email on my own...I did have it in the original I believe.

I guess the last piece of the puzzle is having multiple 'feeder' PM files?

 

[Nebu]

Hi:

Please find the attached. The name of the PM files are there in the MASTER sheet you can add more PM file names to this column as per your requirement and save all the PM files in the same folder as MASTER file the macro will work fine ....

 

[Matthew Berg]

This error was returned...

 

[Matthew Berg]

Is it possible for the "PM" files to be closed? Opening 7 Excel files starts to bog down my system. Maybe I'll try my hand at a macro that will do that.