LinkBack URL
About LinkBacks
I would like to apply the filter "Greater than 0" to column W and then automatically print the document.
I would like to apply the filter "Greater than 0" to column W and then automatically print the document.
Here are the files.
Last edited by DAULT; 12-07-2017 at 03:16 PM.
Do both Columns I and Column J in Job List have to be greater than 0 for the row to be visible or can only one of the 2 columns be greater than 0 for it to be visible?So, step-by-step, this is what my goal is.
-The macro would do the following:
-Filter the Job List based Column I and Column J. The filter would be 'greater than 0'.
-Open each Job Cost Ledger associated with the project numbers from the filtered results from Step A.
-Within each Job Cost Ledger, carry out the following steps:
-Filter Job Cost Ledger to display 'greater than 0' for Column U9:U as well as Column W9:W and Y9:Y. Columns U and V must filter using the merged cells if possible.
-Print the document.
-Clear filter.
-Close the document and proceed to next Job Cost Ledger.
Do Columns U, W and Y all have to be greater than 0 for the row to be visible or can only one or two of the 3 columns be greater than 0 for it to be visible?
Filtering with the merged cells (columns U and V) is going to be a problem. I'll play around with it and see what I can do.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
I think if either of the cells has data they should be displayed. Essentially by deleting the data first the cells without an associated Job Cost Ledger will stay empty.
Columns U, W and X should all be kept if any of them have any data of any format. That will eliminate some of the issues with the merged cells. By replacing Y with X, that should help since both rows from the period will have data if either of them do.
Without reformatting and recreating the entire Job Cost Ledger and formulas I am not sure how else to eliminate the merged cells.
I think that I have managed to solve the problem with the filtering with the merged cells. I have to unmerge the cells in columns U and W. Then I need to create a helper column in column AA duplicating the values from column U in both rows that were previously merged and then filter on AA instead of U. Then AA can be cleared because it isn't needed after the filtering. My time is getting a little tight so I'll work on it when I can and respond as soon as possible. It would be strongly advisable to avoid merged cells completely. Do a quick search into "Centre Across Selection". Sometimes this can help avoiding merged cells.
Here is another option. What if I presented a date cell in the ledger that was the start date of the project. Then a filter could be applied to HIDE all rows prior to the start date and after then current date.
After thinking about this some more, I think this might be the better options as then during the project duration there would also be representation of when nothing was done.
OK. Before I start working on my previous idea, attach a revised Ledger workbook with the necessary changes and explain again in detail how you want it to work.
Ok. Sorry for the confusion on this, but I am trying to help to simplify your work because I really appreciate the effort you are putting in to this. There really is no change to the form other than noted below.
If Cell B8 contained the start date of the project, then the filter could be applied to keep ALL rows where the date in Column B9:B is between the date in B8 and the current date.
I realize I would have to revise the formulas in Column B that calculate the dates, but I can work on that side of things if you can make the filter work.
Last edited by DAULT; 12-06-2017 at 01:03 PM.
Sounds good. Give me a little bit of time and I'll get back to you as soon as I can.
There is a problem filtering on both columns I or J. It's possible but requires slightly modifying the page. If there is a value in column I of the Job List, will you get the desired result if the data is filtered only on column I being >0 ?
I think that should be fine. I don't see where there could be data in Column I and not J because I is the value of the work we have completed and J is the value of our invoicing to date.
OK. While I work on the filtering, here is a line of code you can add to clear the contents of Column H2:H and Column I2:I
Place this line:above this line:
Please Login or Register to view this content.Please Login or Register to view this content.
Perfect. How can I eliminate the following error:
"We can't update some of the links in your workbook right now. You can continue without updating their values, or edit the links you think are wrong"
The error appears because there are links to future timesheets. Since we will have new timesheet files for each year I didn't want to have to go into every active project to update the links in the formulas. Therefore each ledger includes two years of dates.
To be honest, I don't know how to eliminate the error message. You can try Application.DisplayAlerts = False ..... Application.DisplayAlerts = True
I noticed a typo in the original macro. Please change to this: Dim srcWB As Workbook
I have a draft macro for you to try. I couldn't test it because of the date issue that you still have to fix in column B. Place the macro in a regular module in Job List. You can place it in the same module you currently have.
Please Login or Register to view this content.
Ok, so something I did do is moved the date from cell b8 to cell s3, however I fixed that in the macro you sent.
The filter of the job list appears to work. I tried creating new files, importing the data using the previous macro and then running the new one and that part works fine.
After the macro opens the first file I receive a Runtime Error '91': Object variable or With block variable not set. Also, the filter in the Job Cost Ledger doesn't apply correctly and ends up not displaying any lines of data even though the date is set to October 31 in cell S3.
Can you attach copies of the files you are using?
Files attached.
What I did was created three ledgers for the first three jobs in the list and then ran the macro from the ribbon.
Last edited by DAULT; 12-07-2017 at 03:17 PM.
Try these macros. I've made some modifications to both. You shouldn't get the update links message.
Please Login or Register to view this content.
Do these both go in the Job List file?
Yes they do. Replace whatever is already there.
Done. Everything seems to function fine without any errors, however the filter on the ledger file is taking away all lines. The attached picture is what the ledger looks like when it prints.
One more thing. One more thing. Can you give me just the portion of the macro to filter then job list and another one to clear the filter? I know I can do this with the filter option in Excel, however if the person using it doesn't know Excel well they can just use buttons I place in the ribbon.
Happy to see that all is working without errors. The picture of the printout wasn't attached.
This will filter:
This will clear the filter:Please Login or Register to view this content.
Please Login or Register to view this content.
Not sure why, but I keep getting an error when I try to attach the image of the filtered file. Essentially what it prints is the portion of the file above Row 9.
Last edited by DAULT; 12-07-2017 at 12:41 PM.
Still no attachment.
Not sure why, but I keep getting an error when I try to attach the image of the filtered file. Essentially what it prints is the portion of the file above Row 9.
The other filters work perfectly. Thank you.
I have some panes frozen in the ledger file - would that make a difference in the filter being applied? Also, the dates are calculated and not text if that would matter.
Try this version:
Please Login or Register to view this content.
It still does the same thing. It is something to do with the filter on the ledger portion. Does the ledger have to be a macro enabled file?
Could it have something to do with starting the filter at A1, since that cell doesn't have a date? The range should start at A9 shouldn't it?
This is what I'm getting (see attachment).
Hmmm... What I get is the top bar with all of the job information, but no dates.
See attached.
I blacked out the information on purpose by the way.
This is exactly how I would like to have it look except that the dates needs to be included and I would like cell at the bottom wiht the job number to remain also.
I just noticed an error in the file you sent to me. The first date is December 2, however then the next date is November 5. The first date in the actual ledger with values is October 9 and December 5 doesn't appear anywhere.
I noticed the date from the job list is October 31, so it won't be displaying time before then from the ledger.
Based on the sample file that I revised for testing and a start date of October 31, the data filtered correctly. Only the dates between October 31 and today appear. The following macro is the best I can do in terms of filtering. I tried different approaches to get the printout to look like your attachment but the merged cells in rows 5, 6 and 7 cause havoc. You could get the Project number to appear if you place it in any row above row 6.
Please Login or Register to view this content.
How much difference would it make if I tried to eliminate the merged cells above? Those ones are easy to merge - the ones below with the dates are the ones that are more difficult.
I really don't know. You could try to unmerge the cells in rows 5, 6 and 7 and try the macro and see what happens.
Well...it works now other than the project number at the bottom of the page...
Thank you so much for your help with all of this. Very much appreciated.
You are very welcome. If I can figure out how to add the project number, I'll let you know.
I think this one will work a little better for you even without unmerging the cells in row 5, 6 and 7.
Please Login or Register to view this content.
Last edited by Mumps1; 12-08-2017 at 10:40 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks