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.

Hi Joshi

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

Instead of only

Hi Joshi

Try the Code in the attached...

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”.

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.

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.

Hi joshi

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

Hi John,

Sorry...its working now. I don't know what happened before. Thank you so much for your time and help.

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

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...

Hi joshi

What is

"CondFormat" macro

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.

Hi joshi

In Macro "Sub Teams()" replace the indicated line of Code

Please Login or Register  to view this content.

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.

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.

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.

Hi John,

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


Thanks a lot once again.

Joshi

You're welcome...