Auto save multiple accounts from one sheet - Simplify

[tlc53]

Hi,

I'm wondering if there's an easier way to do this, which is user friendly and can cope with new accounts being added. Currently I have the below code, which works great. However, I have 100 accounts and the below code is for just 3 accounts. It is going to be very time consuming writing all that code and then if a new account is added, I would need to add new code for it.

This is how it currently works.
I have a sheet called "Dashboard". From here, you can automatically save each individual account by checking a command button (hence where below code comes in). There are approximately 100 sheets which relate to each account. I have named these in the format letter-letter-letter-number-number eg. ABC01
Currently the sheet/account name is hardwired into the code. Is it possible for it to recognise the sheet name by a cell reference on the "Dashboard"? This is located in column A.
Also, the ActiveX command button is located on the same row (column J) it needs to obtain all it's information from.
The first one for eg, AAI001
A12 = AAI001 (sheet/account name)
M12 = File save path
N12 = File save name
J12 = Checkbox to run code
L12 = Cell to confirm when file saved

On my dashboard at the bottom of the table I have allocated 10 spaces for any new accounts to be added. I have pre-populated the account names from ZZZ01 to ZZZ10

Is there a more condensed way to write this code and maybe for it to understand it should get its data from the same row the command button is on (perhaps it needs to be a form control rather than an ActiveX).

Thank you!

Private Sub CheckBox1_Click()
  If CheckBox1 Then
    Call SavePdf1("AAI01")
    CheckBox1.Value = True
  End If
End Sub


Sub SavePdf1(wSheet As String)
  Dim wfolder As String, wfile As String
  wfolder = Range("M12").Value
  wfile = Range("N12").Value
  If wfolder = "" Then
    MsgBox "Enter folder"
    Exit Sub
  End If
  If wfile = "" Then
    MsgBox "Enter file name"
    Exit Sub
  End If
  If Dir(wfolder, vbDirectory) = "" Then
    MsgBox "Folder does not exists"
    Exit Sub
  End If
  If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
  If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
  Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  MsgBox "The file has been saved"
  CheckBox1.Enabled = False
  Range("L12").Value = "Saved " & Date
End Sub


Private Sub CheckBox2_Click()
  If CheckBox2 Then
    Call SavePdf2("ADI01")
    CheckBox2.Value = True
  End If
End Sub


Sub SavePdf2(wSheet As String)
  Dim wfolder As String, wfile As String
  wfolder = Range("M13").Value
  wfile = Range("N13").Value
  If wfolder = "" Then
    MsgBox "Enter folder"
    Exit Sub
  End If
  If wfile = "" Then
    MsgBox "Enter file name"
    Exit Sub
  End If
  If Dir(wfolder, vbDirectory) = "" Then
    MsgBox "Folder does not exists"
    Exit Sub
  End If
  If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
  If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
  Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  MsgBox "The file has been saved"
  CheckBox1.Enabled = False
  Range("L13").Value = "Saved " & Date
End Sub


Private Sub CheckBox3_Click()
  If CheckBox3 Then
    Call SavePdf3("ADI02")
    CheckBox3.Value = True
  End If
End Sub


Sub SavePdf3(wSheet As String)
  Dim wfolder As String, wfile As String
  wfolder = Rangen("M14").Value
  wfile = Range("N14").Value
  If wfolder = "" Then
    MsgBox "Enter folder"
    Exit Sub
  End If
  If wfile = "" Then
    MsgBox "Enter file name"
    Exit Sub
  End If
  If Dir(wfolder, vbDirectory) = "" Then
    MsgBox "Folder does not exists"
    Exit Sub
  End If
  If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
  If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
  Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  MsgBox "The file has been saved"
  CheckBox1.Enabled = False
  Range("L14").Value = "Saved " & Date
End Sub

 

[tlc53]

Glad it's working.
The .autofit line is what is setting the column widths after each save. Remove it.
Also, selecting is not necessary for most VBA actions and it slows processing a bit. Change your column setting code to:

Thanks so much! Everything is working great!
I also appreciate your comment about not having to select, to keep things moving faster.
Thanks again and have a nice weekend