Clean up and add to VBA code

[Tangled1]

Is a more efficient way to wrote my working code below? I'm pulling specific rows of data based on a column value from the master sheet and pasting it to a completely new workbook. By the way I wrote this code through a lot of trial and error as I'm not great with V

I am trying to pull data from the master sheet for 11 other "grades" I.e. grade 2, grade 3 etc. How can I add to this code to ensure it follows the same rule as the example? They need to be pasted into a new tab each time and and have in Red text "PRIVATE INFORMATION" between Range B2:F4 in each tab

 'WORKING COPIES FILTER DATA TO NEW WORKBOOK AND PASTE TO A5, renames sheet, then clears filters in main doc before saving and closing the doc

Private Sub CopyItover ()

With Workbooks ("FileName.xlsx") . Worksheets ("Team Data"). ListObjects (1)
.Range .AutoFilter Field:=7, Criterial:="Grade 1"
Workbooks ("FileName.xlsx"). Worksheets ("Team Data") .Range("B:H").Copy

Set NewBook = Workbooks. Add
Set NewBook = ActiveWorkbook

NewBook.Worksheets ("Sheet1") .Range("A5") .PasteSpecial (x1 PasteValuesAndNumberFormats)
ActiveSheet.name = ActiveSheet.Range ("F6")
Workbooks ("FileName.xlsx"). Worksheets ("Team Data"). ShowAllData
NewBook. SaveAs Filename:="\\FilePath\ Team Report XEnterDatex.xlsx"
Workbooks ("Team Report - xEnterDatex").close

End With
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your "Team Data" sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

have in Red text "PRIVATE INFORMATION" between Range B2:F4 in each tab

Please explain in detail what you mean by the above statement. You want a separate workbook for Grades 1 to 12. Is this correct?

 

[Tangled1]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your "Team Data" sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Please explain in detail what you mean by the above statement. You want a separate workbook for Grades 1 to 12. Is this correct?

Hi Mumps. Thanks for responding. I managed to figure out the code myself in the end and it works as I needed

 

[Fluff]

Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.