So, I have 6 “master” files to then divide into 40 separate files

by

Try something like this (tried to stick to your style/approach)

'open new workbook
Set NewCaseFile = Workbooks.Add

'-------------------------------------------------
Dim strSheetNameAG As String
Dim strSheetNameER As String
'etc

Dim intLoop As Integer

For intLoop = 1 To 40

    'set sheet names
    strSheetNameAG = "HR gp " & i
    strSheetNameER = "F&B gp " & i
    'etc

    'move them across
    Windows("AG.xlsx").Sheets(strSheetNameAG).Move Before:=NewCaseFile.Sheets(1)
    Windows("ER.xlsx").Sheets(strSheetNameAG).Move Before:=NewCaseFile.Sheets(1)
    'etc

Next intLoop

'-------------------------------------------------
'Save the created file for Group1
 ActiveWorkbook.SaveAs Filename:=sPath & "gp 1.xlsx", FileFormat:= _
   xlOpenXMLWorkbook, CreateBackup:=False
   ActiveWorkbook.Close False

More Related Contents:

  1. delete Autofilter Field resullt using the header Name
  2. Somehow it’s not copying [closed]
  3. VBA macro that search for file in multiple subfolders
  4. Assign code to a button created dynamically
  5. Last not empty cell (column) in the given row; Excel VBA
  6. HTML Text with tags to formatted text in an Excel cell
  7. Split comma separated entries to new rows [closed]
  8. Get list of Excel files in a folder using VBA [duplicate]
  9. Select first empty cell in column F starting from row 1. (without using offset )
  10. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  11. Excel VBA: AutoFill Multiple Cells with Formulas
  12. VBA.Find and adding a value
  13. excel vba- extract text between 2 characters
  14. Round function in Excel, worksheet function vs VBA
  15. How to continue the code on the next line in VBA
  16. Excel VBA Userform – Execute Sub when something changes
  17. Count the number of rows in another sheet [duplicate]
  18. Getting Excel to refresh data on sheet from within VBA
  19. Range.Find not making a difference between January and November (February and December) in VBA Excel
  20. VBA Password Input with Cancel Function
  21. Finding first blank row, then writing to it
  22. How can I display my windows user name in excel spread sheet using macros? [closed]
  23. How does Excel’s worksheet password protection work
  24. Calling regasm without administrative rights for COM interop in Excel VBA
  25. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment