Autofiltermode problems - working on some worksheets but not others

[NateS]

I am working on a project for work and I am having problems with some VBA I have written. My project has a "database" of projects in a portfolio on one sheet and on other sheets I am generating reports by Project Lead Group. I am using autofiltermode to filter my "database" by "Current" projects, "Active" projects, and the by Project lead group.worksheet and then copy and pasting values of specific columns to the report worksheets. Once all current and active projects for a Project lead group are copied, pasted to the report worksheet, and sorted by Project Lead name, it is autofiltered again for projects with due dates in the next two weeks. These projects with dues dates in the next two weeks are then copied and pasted to another report on the same Report worksheet. Each worksheet generates a report for a different Project Lead group (Drilling & Completions, Reservoir & Analytics, Operations, Facilities, CE Project, and Geology). The code is triggered by the click of an Active X command button on each sheet. The code below works for all worksheets expect for CE Projects and Geology. However, the problem is different for CE Projects than it is for Geology.

On the CE Projects WorkSheet the code successfully autofilters and copies all CE Projects from the database to the report worksheet. However, on the report worksheet it fails to only copy the projects with due dates in the next to weeks to the appropriate report. Rather is it copying all rows. I have checked ranges and columns many times and tried to troubleshoot this for several days with no success.

On the Geology worksheet the problem is the opposite. It is not filtering only geology projects from the Project Lead Group column but rather copying either all current, active projects or all projects which have Project Lead Groups with spellings after Geology alphabetically. This occurs when the database is sorted by Project lead group and Project lead. When the database is sorted by project number it copies all current active projects. The code to filter, copy and paste projects with due dates in the next two weeks is working correctly on this worksheet.

Below is the code I am using. It is the same for each page except for changing the ws2 name and the autofilter criteria for the Project Lead group.

Please Login or Register  to view this content.

I'm sorry I posted before I finished writing my message. I wanted to say that any help would be appreciated and thank you all ahead of time for any help.

Best Regards,

Nathan

[NateS]

Any advice would be greatly appreciated. I am at a loss as to why this works on multiple sheets but only two are having problems.

Thank you ahead of time for all your help.

[TMS]

Please add code tags to your OP to comply with forum rules.

When you use the construct With ... End With, you need to "connect" the range to the worksheet by using a full stop/dot.

Not sure why it would work on some worksheets and not others.

See the amended, but untested, code below:

Please Login or Register  to view this content.

Regards, TMS

[NateS]

TMS,

Thank you for your response. I just got back online and I will give this a shot. I appreciate your help.

Best Regards,

Nathan

[NateS]

TMS,

It still is not filtering on "Geology". Do you know what might cause this? Is there another method other than using Autofiltermode which could achieve the same result?

Best Regards,

Nathan