Firstly I hope I've placed this in the correct forum.
Secondly, I apologise for the very messy code below, its a result of 'gathering' various other code and recording macros to get this to do what I require.
I've got a workbook, with a couple of worksheets in, 1 of which is a dashboard, so the macro below grabs the staff listed in the other worksheet copies them to a new one then filters the list and returns them, once its done its sorts into alaphbetical order and then copies a formula down each adjacent cell.
As I'm sure many will tell, I'm not great with VB scripting, but this does work, it worked much better before I remembered I had to share the workbook, hence the copy out to a new sheet. The bit I'm stuck on is
For some reason it just errors, I've tried various combinations but I just can't get it to work.
Any help greatly appreciated, the whole script is below.
Sub tester_filter() 'Filter list of testers from Plan to dashboard and show how many line items each one is working on 'Set file path for temp worksheet (if it doesn't exits just create temp.xls and enter file path below) Dim ThisFileName, Temp As String ThisFileName = ActiveWorkbook.Name Sheets("Dashboard").Select Range("j2").Select Temp = ActiveCell 'Check that filename has been set If Temp = "" Then MsgBox "Please set the temp file location before you continue", vbCritical, "File Location" End Else End If If Dir(Temp) <> "" Then MsgBox "Temp file found. Click OK to continue", vbInformation, "Success" Else MsgBox "Temp file doesn't exist, please check the file path & name and try again", vbCritical, "File not found" End End If 'Turn off screen flicker Application.ScreenUpdating = False 'Select worksheet dashboard and delete current list Sheets("Dashboard").Select Rows("24:100").Select Selection.Delete Shift:=xlUp 'Select Plan and range of testers Sheets("Plan").Select Range("D3:D200").Select Selection.Copy 'Filter range to new sheet Workbooks.Open Filename:=Temp Range("a1").Select ActiveSheet.Paste Range("a1:a200").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "b1"), Unique:=True 'Select range on sheet Range("b2:b200").Select Selection.Copy 'Select UAT Prep dashboard worksheet and paste new filtered list 'Windows("New UAT Prep Sheet - Sandbox.xlsm").Activate Windows(ThisFileName).Activate Sheets("Dashboard").Select Range("B24").Select ActiveSheet.Paste 'Sort list into alphabetical order ActiveWorkbook.Worksheets("Dashboard").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Dashboard").Sort.SortFields.Add Key:=Range("B24") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Dashboard").Sort .SetRange Range("B24:B50") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Add Auto Filter to Plan Sheet Sheets("Plan").Select Range("A2:T2").Select Selection.AutoFilter 'Select dashboard, update formula and draw borders Sheets("Dashboard").Select ActiveWindow.ScrollColumn = 1 RowCount = Application.WorksheetFunction.CountA(Range("b24:b100")) Range("c24").Select ActiveCell.FormulaR1C1 = "=COUNTIF(Plan!R3C4:R200C4,RC[-1])" Range("c24").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("c24").Borders(xlEdgeTop).LineStyle = xlContinuous Range("c24").Borders(xlEdgeRight).LineStyle = xlContinuous Range("c24").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("c24:c" & RowCount + 23).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom End With Selection.FillDown Range("a1").Select 'Close temp work sheet Workbooks(Temp).Close SaveChanges:=False 'Turn on screen updating Application.ScreenUpdating = True End Sub