Saving multiple Excel files based on a list Solved/Closed

Question

Hi,

I'm totally new to writing macros in excel but I thought I'd give it a go. I've seen similar threads but I can't find the answer to my problem. I want to save the content of a tab, "Master" to a new file based on a list of names in another tab, "Names". I want the filename and the name of the tab in the new file to be named according to the list.

I have based the code on two examples from @TrowaD but I cannot get it to loop correctly. It succeeds the first time but then it gets stuck on Sheets("Master").Range("A1").CurrentRegion.Copy according to the debugger. Error message : Run-time error 9, Subscript out of range. It also opens the newly created file which I would like to avoid if possible.

Any help on this would be very welcome!

Sub SplitData()Dim wbName As StringDim lRow, x As IntegerlRow = Sheets("Names").Range("A" & Rows.Count).End(xlUp).Rowx = 0Do      x = x + 1      wbName = Sheets("Names").Range("A" & x)          Sheets("Master").Range("A1").CurrentRegion.Copy      Workbooks.Add      Worksheets.Add      ActiveSheet.Name = wbName      Range("A1").PasteSpecial      ActiveWorkbook.SaveAs Filename:="/Users/kenth/Documents/exceltest/" & wbName & ".xls"      Application.CutCopyMode = False Loop Until x = lRowEnd Sub

Best regards,
KenthH

TrowaD · Answer

Hi KenthH,
-

 The error you get happens because the newly added workbook isn't closed and doesn't contain a sheet named "Master". This is solved with the point below.
 To prevent having to close all the newly created workbooks, I added a line to close them for you.
  Your path "/Users/kenth/Documents/exceltest/" doesn't have a station letter and contains forward slashes instead of backward. But you didn't mention this, so I guess it's just a sample path. 
Here is the amended code:
Sub SplitData()
Dim wbName As String
Dim lRow, x As Integer

lRow = Sheets("Names").Range("A" & Rows.Count).End(xlUp).Row

Do
    x = x + 1
    wbName = Sheets("Names").Range("A" & x)
    Sheets("Master").Range("A1").CurrentRegion.Copy
    Workbooks.Add
    Worksheets.Add
    ActiveSheet.Name = wbName
    Range("A1").PasteSpecial
    With ActiveWorkbook
        .SaveAs Filename:="C:\MyFiles\" & wbName & ".xls"
        .Close
    End With
Loop Until x = lRow

Application.CutCopyMode = False
End Sub

How does the code perform now?

Best regards,
Trowa

TrowaD · Answer

Hi Kenth,

I don't have acces to a Mac, so I can't test my suggestion.

It seems that for Excel 2011 the path needs to be:
Macintosh HD:Users:RDB:Desktop:YourFolder:
And for Excel 2016:
/Users/RDB/Desktop/YourFolder/

It also seems that Mac doesn't need file extention (which seems very weird to me).

Looking at your first post I would change code line 16:
.SaveAs Filename:="C:\MyFiles\" & wbName & ".xls"
into:
.SaveAs Filename:="/Users/kenth/Documents/exceltest/" & wbName

Hopefully that solves it for you.

Best regards,
Trowa

Saving multiple Excel files based on a list

2 responses

Similar discussions

Subscribe To Our Newsletter!