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
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
VBA Code:
'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