Create new workbooks from names in list

[sfos83]

Hi all,

I was simply hoping to simply create new workbooks (xlsx) each named after a cell in column A, and save them to a drive and close them.
It will need to loop through each line and assign the new workbook that cell value as a name; I've tried all variations of code, and it usually stumbles at the 'SaveAs' step for multiple reasons.

- The length of the list in column A will be variable (between say, 1 and 20 files)
- I'd like to save it in directory "C:\Users\Steve\Desktop\PA Test Folder\Test Files\OneDrive Files\"

Could someone please help? I'm going crazy, thanks!
Ss

 

[Jimmypop]

Welcome to Mr. Excel...

Try the below...

Private Sub CommandButton1_Click()
Dim ws As Worksheet, i As Long, x As String
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationAutomatic
End With
Set ws = ActiveSheet
With ws
    For i = .Range("A" & Rows.Count).End(3).Row - 1 To 1 Step -1
    x = .Cells(i + 1, "A").Value
        If .Cells(i + 1, "A") <> .Cells(i, "A") Then
            Workbooks.Add
            ActiveWorkbook.SaveAs "C:\Users\Steve\Desktop\PA Test Folder\Test Files\OneDrive Files\" & x & ".xlsx"
            ActiveWorkbook.Close True
        End If
        .Activate
    Next i
End With
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationManual
End With
End Sub

 

[sfos83]

Thanks so much for your reply, works a charm!

Steve

 

[sfos83]

Thanks so much for your reply, works a charm!

Steve

Spoke too soon!
Worked a couple of times, now when it runs it closes the sheet in which it is running?
I've not changed any of the code, which is odd....

 

[Jimmypop]

Will have a look again later...

 

[Jimmypop]

Spoke too soon!
Worked a couple of times, now when it runs it closes the sheet in which it is running?
I've not changed any of the code, which is odd....

I retested now... I even extended the amount of files to be created to about 30.... On my side it creates the files and keeps original sheet open from where filenames are located...

As you stated you did not change any of the code... So do you have any other code / macros running together with this?