+ Reply to Thread
Results 1 to 7 of 7

Macro that will work on different sheets week to week?

  1. #1
    Registered User
    Join Date
    06-01-2017
    Location
    Minnesota
    MS-Off Ver
    Excel for Mac 2011 Version 14.7.4
    Posts
    4

    Question Macro that will work on different sheets week to week?

    I receive a spreadsheet every week from a vendor with different sheets for different product categories.
    There are 20 possible categories that they will send me, but most weeks they will only send a random 10 out of the 20.
    There's a lot of manual sorting I have to do so I created a macro to go through each of the sheets and do the sorting. But that macro only includes the categories they sent me that week. I tried adding in the other categories so that if the vendor sends me a spreadsheet that includes those, my macro will still work and not exclude anything.
    However, I get an error message when I try running my macro because it's telling Excel to work on a sheet that isn't there. Is there some magical coding I can do to make this work with the changing sheets week to week?

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,473

    Re: Macro that will work on different sheets week to week?

    It's hard to see the code you are referring to, but there usually is a solution.

  3. #3
    Registered User
    Join Date
    06-01-2017
    Location
    Minnesota
    MS-Off Ver
    Excel for Mac 2011 Version 14.7.4
    Posts
    4

    Re: Macro that will work on different sheets week to week?

    Can I paste my code here so you can take a look?

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,285

    Re: Macro that will work on different sheets week to week?

    If you paste code please enclose it in [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags. It would be even better if you attach your file.

    Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window and submit your post.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Registered User
    Join Date
    06-01-2017
    Location
    Minnesota
    MS-Off Ver
    Excel for Mac 2011 Version 14.7.4
    Posts
    4

    Re: Macro that will work on different sheets week to week?

    Alright, here's super basic code for the first step of the process. There are about 12 other sheets that may or may not be included on a weekly basis.
    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 06-01-2017 at 10:32 AM. Reason: put code inline

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,285

    Re: Macro that will work on different sheets week to week?

    As a new user, you may not realize that it's much easier to simply paste your code into your post, rather than creating a Word document, pasting the code, saving it, then uploading the file. I have put your code right into the post for everyone's convenience.

    coding I can do to make this work with the changing sheets week to week
    The code does not give any insight as to the problem you are trying to solve. You need to describe what are the things that change each week, and how the code will be able to determine what categories apply and how that relates to the worksheets. I still think you should attach your Excel file as an example and explain how you are doing this manually today.

  7. #7
    Registered User
    Join Date
    06-01-2017
    Location
    Minnesota
    MS-Off Ver
    Excel for Mac 2011 Version 14.7.4
    Posts
    4

    Re: Macro that will work on different sheets week to week?

    I can't attach the Excel since it's full of sensitive information.

    Right now this code is just going through each sheet and adding three columns to the end.

    I tried adding this bit of code to the end of my macro, replacing "new" with the other possible names of sheets so if next week my vendor sheet includes one of those other sheets, I won't have to redo my macro:
    Please Login or Register  to view this content.
    But once it gets to the portion of the code for a sheet that's not in the current workbook, I get an error message.

    Is there a way to write the code so that it will just go through the sheet options and add the columns in if the sheet is there?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro that displays all the weeks between Start Week and End Week (ends in next year)
    By Tona in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-31-2014, 05:38 AM
  2. Replies: 6
    Last Post: 05-16-2014, 11:30 AM
  3. How do i run a macro once a week when work book is opened
    By I_amr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2013, 09:45 AM
  4. Sheets for every week + start and ending date of the week
    By wimexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2012, 01:48 PM
  5. How to: identify repeat offenders within a 3 week period (week per week basis)
    By Heloc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2012, 12:43 AM
  6. Macro worked last week, won't work today!?
    By Jackdaddy0711 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2012, 06:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1