Copy a Range to a New Worksheet Retaining Formulas and Formatting

Hello, I have inherited some code that will take a worksheet, filter on col A, pasting the data into a new worksheet, which is then saved as a new file. It works, however, it's just pasting formatting.. it's not retaining any formulas or columnwidth settings.. How can I update the copyto code below to retain the formatting, formulas and column? Thanks!

Please Login or Register  to view this content.

It looks like the code above should be extracting the distinct values from 'PM_TL Evaluations - raw files'!A:A into a newly added worksheet, then iterating through those distinct values in cell A2 and below, creating new worksheets for each of those distinct values, filtering 'PM_TL Evaluations - raw files'!A:L to put all records for the given distinct value into the new worksheet, copy that worksheet as a separate workbook, save that new workbook, close it, delete the original worksheet from which the copy was made. A bit inefficient.

There are multiple Worksheets.Add operations. Is the statement

wsData.Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True

working correctly? I'll assume so, meaning that I'm assuming the problem is inside the While...Wend loop. IOW, I figure the following is the problem.

wsData.Range("A1:L" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), CopyToRange:=wsNew.Range("A1"), Unique:=False

If so, the problem would appear to be in the CriteriaRange. However, I have no idea what's in that range, so no way to confirm that.

Note: advanced filters copying filtered data to other locations don't change column widths in the destination. Your macro needs to set column widths after running the advanced filter.

You need to tell us what the value of 'PM_TL Evaluations - raw files'!A1 is, then what the values of subsequent cells in that column are which produce no filtered values in generated workbooks. Otherwise impossible to diagnose possible problems with the criteria range.

Sorry for the delay. We came up with a workaround solution in order to meet our deadline; however, I'd still like to improve the efficiency of this code if possible as I use it on a weekly basis for breaking out projects details into individual files (I repurposed it for these evaluations). To answer your question, it works.. it's just that the copytorange does not retain any formulas or formatting (column widths). For my project details process, this is perfectly fine, but I needed the formatting and formulas retained on the evals What would you suggest to improve the efficiency of the overall process? The sheet being processed has info like: Project, Employee, Task, Hours Worked, Transaction Comments... The code, filters by project, saving each project into it's own separate file. Any help to improve would be greatly appreciated. Thanks!