We run an audit file for many clients and it produces a team list for each client. From this I hope to have a macro that will export an excel document based upon the client I select and save the document away with a static path eg. C:Team_Audit\ & <field name selected> automatically.
Currently I need several macros and I need to pick the client name from a field then click the corresponding macro so that it is saved away in the same location as the rest, but then saved as the client name hard coded in quotes within the macro. New clients come along frequently and as such I need to re-write a new macro. I would prefer one button that saves same location again and again, but will store away the excel name based upon a specific field record I select.
The previous code is missing the drop of instanced com-objects which should be like follows(starting from XLDoc.SaveAs so you know where to start copy-paste):
' Save the excel-file with the dynamic path and filename XLDoc.SaveAs Path & FileName ' Closes the active document XLDoc.Close ' Close the instanced object references of excel set rngStart = nothing set XLDoc = nothing ' Exits the current running Excel XLApp.Quit ' Closing final object reference set XLApp = nothing next end sub