News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Wood Roundup file – issue with macros?

    Posted on WSk32rem Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Wood Roundup file – issue with macros?

    Topic Resolution: Resolved

    This topic contains 20 replies, has 5 voices, and was last updated by  zeddy 5 days, 17 hours ago.

    • Author
      Posts
    • #2022988 Reply

      WSk32rem
      AskWoody Lounger

      Can someone tell me what needs to be changed on the “Year Report” and “Year Loads” tabs on the attached file to get the pages to update properly?  We used to go from January to December for the Fiscal year for bonuses; they changed it for 2020 to begin in December 2019 and going to November 2020.  I don’t know enough about macros to know how to point the cells to the correct data.  All the other pages update properly and work very smoothly, just need to get the two “yearly” pages fixed.  Thank you in advance for whoever can help!

    • #2023028 Reply

      Kirsty
      Da Boss

      It looks like your spreadsheet didn’t make it. Perhaps you could add a link to a cloud-stored copy, so we can see if we can help you?

      You don’t say if your reports are using Pivot Tables, in which case you might need to alter the Dates on Grouping.

    • #2039721 Reply

      WSk32rem
      AskWoody Lounger

      I don’t know how to “add a link to a cloud stored copy”, and I’m not understanding how to attach a file after selecting it.  I see the name and size, and then 3 links (set file caption, insert into content, remove this file).  And when I hover my mouse over the .xlsm in the box to the left of that, it displays the file I need to attach, but it appears it does not upload.  What am I missing?  It used to be so straightforward.  Please advise.  Thank you!  (In the meantime, I don’t want to lose this reply so I am going to submit it and then search this site to see if I can find an answer)

      (Also, after one of you is able to see the file after I learn how to upload, I am now having issues with Column H on the Delivery Log populating correctly; therefore, the weekly supplier and weekly totals reports are not updating, understandably.)

      Thanks!

    • #2039722 Reply

      PKCano
      Da Boss

      Click on the “Select File” button on the left below the entry box.
      It should open File Explorer on your computer.
      Highlight the file you want to upload.
      Click on the “Open” button in the File Explorer popup window.

      That should attach the file you want to upload.
      And Submit your post.

    • #2040261 Reply

      zeddy
      AskWoody_MVP

      Hi

      Still there and using the Wood Roundup file after all this time?????

      Where’s my lumberjack shirt?????

      This is what you need to do..

      1. in the named cell [yearCell] on the sheet [Parameters] i.e. cell [C8], enter the year as 2020

      2. On the [Calendar] sheet, just change the start date in cell [A2] to Dec 1st 2019 (for the start date of the new fiscal year – this will generate the from-to-week-number list using formulas)

      3. On the sheet [Year Report], edit the formulas in row 10 to give the correct months..

      change..

      [B10] to =DATE(yearCell-1,12,1) ..this will then display your start month as Dec

      [C10] to =DATE(yearCell,1,1) ..this will display Jan etc etc etc

      [D10] to =DATE(yearCell,2,1)

      [E10] to =DATE(yearCell,3,1)

      [F10] to =DATE(yearCell,4,1)

      [G10] to =DATE(yearCell,5,1)

      [H10] to =DATE(yearCell,6,1)

      [i10] to =DATE(yearCell,7,1)

      [J10] to =DATE(yearCell,8,1)

      [K10] to =DATE(yearCell,9,1)

      [L10] to =DATE(yearCell,10,1)

      [M10] to =DATE(yearCell,11,1) ..this will display Nov

      4. . On the sheet [Year Report] copy [B10:M10] and paste in cell [B10] on sheet [Year Loads]

      If you need more help, or want me to post an updated file then I would like a size L shirt from Hawaii’s Jams World store. I could collect it when I’m in New York later this month.

      zeddy

    • #2040279 Reply

      zeddy
      AskWoody_MVP

      Hi

      I am now having issues with Column H on the Delivery Log populating correctly; therefore, the weekly supplier and weekly totals reports are not updating, understandably

      In cell [H11], use Format>Cells to change the [H11]  cell-format from Accounting, 2 decimals (or whatever it is) to General. Then, with the cellpointer in cell [H11], use your mouse to carefully double-click the bottom-right-corner-edge of the cell (the cursor will change when you are exactly on the bottom-right-edge-corner of the cell) to copy that format (and formula) all the way down to the bottom of the column H data range.

      When I said I’d be in New York, it’ll be Manhattan, not anywhere near any Lakes.
      zeddy

    • #2041796 Reply

      WSk32rem
      AskWoody Lounger

      Hi Zeddy!  So good to hear from you again!  And, yes, I am still using the Wood Roundup file after all this time.  It’s been working great until the boss decided to change the fiscal year from January to December.  (Actually, it’s from a Thursday to the first Wednesday in December – not sure if that’s making a difference or not, but I wouldn’t think so).  Anyway, I’ve completed all the steps you listed in your two posts but there must be something that needs to be changed in one or more of the macros because it’s still not working correctly.  I’m hoping I can upload the latest file for you to look at.   Please let me know if it doesn’t upload.  I used to have an email for you but don’t think it’s relevant anymore.

      Lumberjack shirt from Hawaii’s Jams World store?  Hmmm……

      • #2041808 Reply

        Paul T
        AskWoody MVP

        If you zip the file it should upload ok.

        cheers, Paul

    • #2041820 Reply

      zeddy
      AskWoody_MVP

      Hi

      The Wood Roundup file I was using was an old one I found from some years ago.

      If you are happy to zip your latest file here (as Paul T suggests) we could work with that.

      Or, if you prefer to keep your data more confidential, you could send a Direct Message to me in this Forum, and then we can exchange files via emails etc etc etc.

      I was kidding about the lumberjack shirt. And my new Jams World Hawaii tropical shirts are already waiting for me in New York!

      zeddy

    • #2042008 Reply

      WSk32rem
      AskWoody Lounger

      Zeddy,

      Okay, I am trying the zip file to see if that works.  If not, I will inquire further on how to send you a direct message.  Let me know if it doesn’t come though.  Thanks!  And I knew you were joking about the lumberjack shirt.  Just thought it was funny.  Enjoy NY!

      Attachments:
    • #2042010 Reply

      WSk32rem
      AskWoody Lounger

      Looks like it made it.  Yay!

    • #2042038 Reply

      zeddy
      AskWoody_MVP

      Hi

      OK, got your zipped file. Had a quick look at it. Seems to run OK here.

      FYI: I changed the numeric cell format to General on the Totals row on sheet [Year Loads] (it is the sum total of monthly loads, not a dollar amount etc etc etc).

      Not having any issues with clicking the buttons on [Weekly Supplier Report] e.g. changing to Murphy Logging and clicking [select current week] button.

      So, what issues are you having with it????

      It’s late evening here in Newcastle UK (still warmer than New York though), so if you reply soon it probably means I’ll reply early tomorrow.

      zeddy

    • #2042441 Reply

      zeddy
      AskWoody_MVP

      Hi

      In my instructions..

      [B10] to =DATE(yearCell-1,12,1) ..this will then display your start month as Dec

      …the -1 is important in the [B10] formula i.e. yearCell-1

      Although the display will still just show as Dec the ‘actual’ cell contains a date value for Dec 1, 2019. This is important  for the Year Report and Year Loads to correctly ‘extract’ matching month-data.

      zeddy

      1 user thanked author for this post.
    • #2042446 Reply

      WSk32rem
      AskWoody Lounger

      Thank you for your prompt reply!  Boy, do I feel stupid!  My first issue was Week #52 showing up in Column H on the Delivery Log when it should be Week #5.  After a closer examination, I realize I entered December dates for those two lines AFTER the first of the  year and didn’t enter the complete date (12/27/19) but just 12/27, so it came up 12/27/20.  So of course it showed as Week #52.  Duh!!

      But the Year Loads and Year Report numbers for December aren’t registering.  January numbers are showing properly but not sure why December’s aren’t.  Also, I have tried several times to change the Totals row on the Year Loads sheet to General, but every time I save the file it pops back to Accounting.  Strange……  I’m attaching the file again since I’ve updated the Delivery Log.

      Thanks again for all your help and your patience with my “duh” moments.  Good to converse with you again and hope you have a great time in Manhattan.  I’m less than a 6 hour drive west (I live along the NY/PA border about 1-1/2 hours south of Buffalo, NY) and have never been to the Big Apple!

      Attachments:
    • #2042504 Reply

      zeddy
      AskWoody_MVP

      Hi

      I’m posting back an updated copy of the first sample file you posted. Can you have a look at this one, while I take a look at your new posted file.

      In the attached file, I haven’t made any changes to the vba code.

      To help with preventing data-entry errors, I have added a conditional-formatting rule for column [D] on the [Delivery Log] sheet.

      In column [D], if you enter a date that is earlier than the start date or later than a Week 53 date, it will now show up with a red-cell background. This is to alert you to a probable mis-typing of the delivery date. You should ensure that no red cells are present in this column. If you don’t have a Week 53 in the particular year this tool is being used for, then simply don’t enter any delivery dates for that week.

      To help with the conditional-formatting formula, I have added two new named ranges for startDate and endDate.

      I hope this helps.

      I’ll have a look at your new file too.

      Not leaving for New York yet – another week or so – but getting prepped.

      zeddy

      Wood-Roundup-XL2013-2020-v1a

       

      • This reply was modified 2 weeks, 4 days ago by  zeddy.
      • This reply was modified 2 weeks, 4 days ago by  zeddy.
      Attachments:
    • #2042509 Reply

      zeddy
      AskWoody_MVP

      Hi

      Just to repeat, goto cell [B10] on sheet [Year Report] and add -1 like this:

      =DATE(yearCell-1,12,1)

      ..and you’ll see the results you were expecting like magic.

      Do the same for cell [B10] on sheet [Year Loads]

      =DATE(yearCell-1,12,1)

      ..and you’ll see the results you were expecting like magic.

      I’ll explain it in detail why this is needed, but my Close Protection Officer is calling for my attention..

      zeddy

       

       

    • #2042512 Reply

      WSk32rem
      AskWoody Lounger

      Zeddy – Got it!!!  I totally missed that “-1”.  All is well!  Thank again so much!

    • #2042539 Reply

      WSk32rem
      AskWoody Lounger

      Yes, I see what you did there!  Spreadsheets for Dummies!  (That would be me, obviously….)  Thanks again, a big load off my mind to have everything working smoothly.

      1 user thanked author for this post.
    • #2044618 Reply

      zeddy
      AskWoody_MVP

      Hi

      You must take a lot of credit for adjusting the file for use each year. Top marks! Must be five years at least! So a big Thanks from me!

      Good news – seems to be that the macros didn’t need adjusting.

      I have tried several times to change the Totals row on the Year Loads sheet to General, but every time I save the file it pops back to Accounting.

      I have discovered why this is happening. It’s to do with the formatting defined in the named range [formatsYTDLoads].

      This named range is on the [Parameters] sheet. It is used to specify the format you want to be used for the Totals row on the sheet [Year Loads] i.e. color, row height, cell number formats etc etc etc .

      So, on sheet [Parameters], in cell [J43], use Format>Cells to change the [J43] cell-format from Accounting, 2 decimals (or whatever it is) to General.

      Now, whenever the [Refresh Loads Report..] button is clicked on the sheet [Year Loads], the vba routine will now use the formats you have chosen in that named range. Similar thing for the [Year Report].

      It’s a fine balance between automating Excel to deal with all foreseeable events, preventing dodgy inputs etc and just being careful with what you do. You know what you are doing, so we don’t need to spend lots of time adding stuff to check you are doing it right.

      Now about “never been to the Big Apple” – are you mad!!!!!! It is a fantastic place to visit. I’ve lost count. Next you’ll be telling me you haven’t been to San Francisco, or Miami, or Las Vegas, ..the list is endless. Get yourself into a 4×4 SUV Nissan Fugitive or whatever they call them latest chunky car rentals and start planning a Route 66 road trip.

      Say Hi to the City of Light for me, and go easy on the Buffalo wings.
      zeddy

      • This reply was modified 2 weeks, 3 days ago by  zeddy.
      1 user thanked author for this post.
    • #2085239 Reply

      WSk32rem
      AskWoody Lounger

      Great!  Changing the Parameters sheet fixed the Year Loads totals issue (of course!).  Thanks again so much!  We’ve come a LONG way over the years and I surely appreciate all the help you’ve given me to make me look good (at least on paper!)  And thank you for the kind words.

      Enjoy your trip to NYC, and I will continue to enjoy my quiet life in northwest Pennsylvania.  Being 72 yrs old, my husband and I are happy with life in the slow(er) lane.  🙂  Yes, I work in southwestern NY but our home is just over the border in PA.  No, haven’t been to LA or San Fran (with all the homeless living on the streets, drugs and crime, those cities are pretty nasty now) but I did visit Miami with my daughter last September.

      Again, many thanks for all your input and help!

    • #2088165 Reply

      zeddy
      AskWoody_MVP

      Happy New Year and good health all round!
      So another 15 years before you think of retirement then?????

      zeddy
      PS I mis-remembered the news, that new car was the Mustang Mach-E Electric, not the Nissan Fugitive – that was about something else entirely!

      • This reply was modified 5 days, 17 hours ago by  zeddy.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Wood Roundup file – issue with macros?

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.