+ Reply to Thread
Results 1 to 19 of 19

VBA Help needed to create separate sheets for each team by year

  1. #1
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    VBA Help needed to create separate sheets for each team by year

    Folks,

    I have a code that will create separate sheets from “SDL_Calendar” sheet for each team and its working fine. Please help me to modify the code to create sheets for each Team BY YEAR based on user selection in Cells “H6”, “H7” and “H8” in “P6_Report” sheet. For filtering BY YEAR Column "D" Can be Used in “SDL_Calendar” sheet.

    Thanks in advance for your expertise



    I have attached the work book of what I am trying to accomplish.



    Thanks to Mr. John (Jaslake), who built this code for me.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by krjoshi; 02-28-2014 at 12:05 AM.
    Cheers,

    Joshi
    Being with a winner makes you a winner

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    Hi Joshi

    Will these Filtered Team Reports be IN ADDITION TO or INSTEAD OF the Team Reports we previously created?
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Re: VBA Help needed to create separate sheets for each team by year

    Instead of only

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    Hi Joshi

    Try the Code in the attached...
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Question Re: VBA Help needed to create separate sheets for each team by year

    Hi John,
    Thank you so much for your time and expertise. Your codes is working excellent. Can you please help me to fix the below issues.

    1.One format is missing in the TEAM’s sheet (blank row between each row record). Must be same as “SDL_Calendar” sheet.


    2.I want Macro to delete all columns (“T” to xlright) from “TEAM” sheets IF ANY date is greater than “P6_Report” sheet cell “H9”.* e.g. if I select “P6_Report” cell “H9” = 2014 then Macro to delete all columns “GZ to xlright”* from “TEAM” sheets since the cell “GZ6” date is 01-Jan-2015 which is greater than 2014*(Refer Range “T6 to xlright” in “SDL_Calendar” sheet to find any date < or >). Picture is attached for your reference.

    3. Macro to do the page setup for "TEAM" sheets. Page size is A3 and fit to one page, Page orientation is Landscape and Center on page Horizontally.


    4.I want Macro to delete all Columns "F" to "S" from all “TEAM"s sheet.

    5.If “P6_Report” sheet cell “H7” = “” (blank) then popup an MsgBox with message “Please select a Team”.

    6.If “P6_Report” sheet cell “H9” < “H7” then popup an MsgBox with message “The Year Finish you selected is Less than Year Start”.

    7.If cells “H8” or “H9” = “” (blank) then popup MsgBox with message “Please select Year Start/Year Finish”.
    Attached Images Attached Images
    Last edited by krjoshi; 03-01-2014 at 03:41 AM.

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    Hi Joshi

    Please see the Code in the attached...you'll need to change the Paper Size in the Code...my Printer doesn't recognize A3. It's set to A4 in the Code.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Re: VBA Help needed to create separate sheets for each team by year

    Hi John,
    We are almost there. One small issue needs to be fixed.

    I have Selected the team = TEAM.A, start year = 2013 and finish year = 2019 then I run the Macro, the TEAM.A sheet was created perfectly based on the above selection.

    But when I change back the Finish year to = 2017 then I rerun the macro, but the year columns for 2018 & 2019 were not deleted (See the attached workbook). Please help me fix this issue

    (For your info: the code is working fine till finish year 2016).

    Thanks once again for your time and help.
    Attached Files Attached Files

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    Hi joshi

    I've run the Code on your most recent File about a dozen times and can't duplicate the issue...

  9. #9
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Re: VBA Help needed to create separate sheets for each team by year

    Hi John,

    Sorry...its working now. I don't know what happened before. Thank you so much for your time and help.
    Last edited by krjoshi; 03-02-2014 at 04:40 PM.

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    You're welcome...glad I could help. Thanks for the Rep.

  11. #11
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Re: VBA Help needed to create separate sheets for each team by year

    Hi John,

    Sorry for bothering you.

    **The below issues is still existing **Once I run "CondFormat" macro**
    Hi John,
    One small issue needs to be fixed.

    I have Selected the team = TEAM.A, start year = 2013 and finish year = 2019 then I run the Macro, the TEAM.A sheet was created perfectly based on the above selection.

    But when I change back the Finish year to = 2017 then I rerun the macro, but the year columns for 2018 & 2019 were not deleted (See the attached workbook). Please help me fix this issue
    Please help me to fix this...

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    Hi joshi

    What is
    "CondFormat" macro

  13. #13
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Re: VBA Help needed to create separate sheets for each team by year

    Hi John,

    There is Macro called "CondFormat", to do some Conditional formatting in "SDL_Calendar" sheet.

    I have attached the file for your reference.

    And code is

    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    Hi joshi

    In Macro "Sub Teams()" replace the indicated line of Code
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Re: VBA Help needed to create separate sheets for each team by year

    Hi John,
    Great. ..The problem is solved now. If you don't mind can you please check and advise why this Macro "Sub Teams()" running very slow.

    Thank you very much for your time and help.

  16. #16
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    Hi joshi

    There are two issues:
    This Printer Communication part of your Code is extremely slow to process. It appears to be recorded Code and I'm not sure why you've added it...
    Please Login or Register  to view this content.
    Second issue, you have almost 1700 Cells with dates the Code is looking at...perhaps I can speed up that process...I'll look at it.

  17. #17
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    Hi Joshi

    See if this "Teams" Code runs faster for you. Notice I've commented out the Print Setup Lines of Code
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Re: VBA Help needed to create separate sheets for each team by year

    Hi John,

    Ohhh Great. The code is running very fast now. Thank you so much GURU..!!


    Thanks a lot once again.

    Joshi

  19. #19
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Help needed to create separate sheets for each team by year

    You're welcome...

+ 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] VBA Help need for Copying data from another sheet and create separate sheets
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-26-2014, 09:27 AM
  2. [SOLVED] VBA / Macro help needed to create separate sheets from Master sheet by year
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-09-2013, 08:34 AM
  3. Replies: 5
    Last Post: 09-30-2012, 07:47 AM
  4. Macro to create separate sheets
    By scauley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2010, 06:07 AM
  5. How do I create a conditional running total on separate work sheets?
    By Poonz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2007, 09:08 AM

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