PDA

View Full Version : [SOLVED] Copying, naming, and saving a master workbook



oam
08-04-2016, 05:33 PM
I need to create multiple workbooks from one master workbook, name the newly created files from the Names worksheet contained within the master workbook, and saving the newly created and named files to a server location.

Is there some code that will do this task?
Thank you for your time.

Kenneth Hobs
08-05-2016, 06:14 AM
There are not enough details to give specific help. You need to tell us where the "names" are at and what are example file names. How would you handle the case where a file with the created name already exists?

FSO's CopyFile is how I would do it. e.g.
TextBox1 value would be your network path. TextBox 2 and 3 are the numerical To From values, say 1 to 10.

Rem Needs Tools > References > MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Private Sub CommandButton1_Click()
Dim i As Integer
Dim FS As Scripting.FileSystemObject
Dim s As String

Set FS = New FileSystemObject

For i = TextBox2.Value To TextBox3.Value
s = TextBox1.Value & " - Lot " & i & ".xls"
FS.CopyFile ThisWorkbook.FullName, s
Next i

Set FS = Nothing
Unload Me
End Sub

oam
08-05-2016, 03:50 PM
The names come from the Names worksheet contained within the Master workbook and are Last, First Initial; example: Smith, James R (no period). As far as "How would you handle the case where a file with the created name already exists?" I had not thought about that but I would replace the current file with the new one in case the master was update with additional/new information. One other thing I had not thought of since you said "How would you handle" is it would be good to add only a new workbook(s) with the addition of a new name(s) and not replace the entire folded contents. Also, taking away workbook(s) when a name(s) is removed from the names list.

Thank you for your help and I hope this answered you questions and makes it a little clearer.

Kenneth Hobs
08-06-2016, 05:13 PM
What column(s) row(s) is/are Last, First Initial? Is it 1, 2, or 3 columns? Once you answer, the solution is trivial.

I don't know how to handle the case of update if new data. That should be no problem though since you want to replace if one exists already so that may accomplish both goals. It will not handle the case where someone modifies the existing file but then the master overwrites it.

oam
08-08-2016, 05:20 PM
The (Last, First Initial) names start in column A row 2.

Kenneth Hobs
08-08-2016, 08:26 PM
Rem Needs Tools > References > MicroSoft Scripting Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Sub Main()
Dim FS As Scripting.FileSystemObject, nwPath As String
Dim c As Range


nwPath = "c:\MyFiles\Excel\Test\"
Set FS = New FileSystemObject

For Each c In Range("A2", Range("A2").End(xlDown))
FS.CopyFile ThisWorkbook.FullName, nwPath & c.Value2 & ".xlsm"
Next c

Set FS = Nothing
End Sub

snb
08-09-2016, 02:31 AM
Use


Thisworkbook.savecopyas

oam
08-09-2016, 07:07 PM
Thank you both for your help, you guys are the best!!