Save excel file multiple times with file names in another workbook.

by

Here is a snippet of code taken from a code I use for something similar – it should help:

Option Explicit
Sub Exportmacro()

Dim rCell As Range, rRng As Range 'define loop names
Dim NewCaseFile As Workbook 'give a name to new work book for duplicate sheet
Dim wks As Worksheet 'name of the copy of feedback
Dim sPath As String
sPath = MacScript("(path to desktop folder as string)")
'turn off screen
With Application
    '.ScreenUpdating = False  ‘only removed while testing
    '.EnableEvents = False
    '.Calculation = xlCalculationManual  ‘disabled for the moment
End With

'Student numbers in cells A7:A160 WARNING SET TO 3 STUDENTS ONLY FOR TEST
Set rRng = Worksheets("studentlist").Range("A7:A9")

    For Each rCell In rRng '<--| loop through "students" range

        'now open new workbook 
         Set NewCaseFile = Workbooks.Add

        'now save as xls with student number as filename Filename:=sPath & rCell.Value & ".xlsx"
         ActiveWorkbook.SaveAs Filename:=rCell.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

        'now close duplicate file
         ActiveWorkbook.Close False

    Next rCell   '<-- next student number
End With         '<-- once all done
'turn screen back on
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Provided to get you started – you need to edit / change it to your own needs.

More Related Contents:

  1. Copy Paste Values only( xlPasteValues )
  2. Loading addins when Excel is instantiated programmatically
  3. Excel’s fullname property with OneDrive
  4. How to insert an embedded picture?
  5. Copy Excel range as Picture to Outlook
  6. Obtain attribute names from XML using VBA
  7. Return RGB values from Range.Interior.Color (or any other Color Property)
  8. excel VBA run macro automatically whenever a cell is changed
  9. Translate text using vba
  10. Run-time error ‘1004’ : Method ‘Range’ of object’_Global’ failed
  11. “Can’t find Project or Library” for standard VBA functions
  12. How do I issue an HTTP GET from Excel VBA for Mac
  13. Copy sheet and get resulting sheet object?
  14. Sending mail using Outlook where the Send method fails
  15. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  16. Using a variable for row in (row,column) for a range in VBA
  17. Export chart as image – with click of a button
  18. Unicode string literals in VBA
  19. How to get the path of current worksheet in VBA?
  20. Excel VBA to match and line up rows
  21. Change font color for a part of text in cell
  22. Create a new object using the text name of the class
  23. Excel cell from which a Function is called [duplicate]
  24. How to extract text within a string of text
  25. How to open a workbook specifying its path
  26. GET pictures from a url and then rename the picture
  27. How can I loop through a subset of worksheets?
  28. How to run a string as a command in VBA
  29. How to Send Email When Computer is Locked?
  30. VBA, Combine PDFs into one PDF file

Leave a Comment