- 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:
Dim wbName As String
Dim lRow, x As Integer
lRow = Sheets("Names").Range("A" & Rows.Count).End(xlUp).Row
x = x + 1
wbName = Sheets("Names").Range("A" & x)
ActiveSheet.Name = wbName
.SaveAs Filename:="C:\MyFiles\" & wbName & ".xls"
Loop Until x = lRow
Application.CutCopyMode = False
How does the code perform now?