Is there a way to do this in VBA without inserting a new Column A?

[bloomfieldhero]

I attached a dropbox link so it’s easy to follow along.



In this roster, I have a sheet labeled setup. In there aremanager names to reference, I want these three managers to be transferred intotheir own workbooks (individually: manager 1, manager 3, manager 5). In Sheet1,Column AR is where you can find the manager names, the rest of the data is justdummy data. I only want the employee data from the criteria in the "Setup" worksheet, nothing else.



I have a script that works when I copy andinsert copied cells into a new Column A (column AR managers), but that forcesextra tedious steps. Can I Just keep the worksheet as-is, and have this scriptreference column AR –to-the Setup worksheet criteria? Or will I have to have my managerlist column be Column A for this to work?

Sub Main()
  Dim Managers, Manager
  Dim Header As Range, Where As Range, This As Range
  Dim Wb As Workbook
  
  'Prepare
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  'Refer to the headings
  Set Header = Range("A1").EntireRow
  'Refer to the data in column A
  Set Where = Range("A2", Range("A" & Rows.Count).End(xlUp))
  'Get the managers
  With Worksheets("Setup")
    Set Managers = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
  End With
  'Loop through
  For Each Manager In Managers
    'Find them
    Set This = FindAll(Where, Manager)
    If This Is Nothing Then GoTo Skip
    'Create a new file
    Set Wb = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
    With Wb
      With .Sheets(1)
        'Copy the header
        Header.Copy .Range("A1")
        'Copy the data
        This.EntireRow.Copy .Range("A2")
      End With
      'Save it
      .SaveAs ThisWorkbook.Path & Application.PathSeparator & Manager & Format(Date, "_mm_dd_yyyy") & "_Roster.xlsx", XlFileFormat.xlOpenXMLWorkbook
      .Close
    End With
Skip:
  Next
  'Done
End Sub

Any idea how to do this?

 

[bloomfieldhero]

https://www.dropbox.com/s/k5s24lco2zsom1a/DUMMY DATA_2017 Mid Year TCR Example.xlsm?d1=0 is the dropbox link

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub Main()
   Dim Managers As Variant, Manager As Variant
   Dim Wb As Workbook
   
   'Prepare
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   
   With Worksheets("Setup")
      Managers = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
   End With
   'Loop through
   With Sheets("Sheet1")
      For Each Manager In Managers
         If .AutoFilterMode Then .AutoFilterMode = False
         If Application.CountIf(.Range("AR:AR"), Manager) > 0 Then
            .Range("A1:BM1").AutoFilter 44, Manager
            Set Wb = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
            .AutoFilter.Range.Copy Wb.Sheets(1).Range("A1")
            Wb.SaveAs ThisWorkbook.Path & Application.PathSeparator & Manager & format(Date, "_mm_dd_yyyy") & "_Roster.xlsx", XlFileFormat.xlOpenXMLWorkbook
            Wb.Close False
         End If
      Next Manager
      .AutoFilterMode = False
   End With
      Application.DisplayAlerts = False

End Sub