Create new workbooks and name them dynamically using VBA...

[kaushik03]

Hi all,


I have four parameters as follows:

AA (at a1)

BB (at a2)

CC (at a3)

DD (at a4)

I want to create four workbooks dynamically one for each parameters. The workbooks should have the name as AA.xlsm, BB.xlsm, CC.xlsm and DD.xlsm.


I have tried it by running a loop to create the workbooks for each parameter and name them accordingly, but the only point I am getting stuck at changing the name of the workbook dynamically each time the loop runs.


Early help from any one of you is highly appreciated.


Looking forward to your reply.


Kaushik

 

[kuldeepjainesl]

You can have a look at ASAP Utilities at http://www.asap-utilities.com if this solve your problem.


Regards,

Kuldeep

 

[nazmul_muneer]

You can check


ActiveWorkbook.SaveAs Filename:="<Drive Name>:<Folder Name><File Name>.xlsm", FileFormat:= _

xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _

, CreateBackup:=False

 

[NARAYANK991]

Hi Kaushik ,


Try the following in the Sheet section of your VBA Project :

[pre]

Public Sub Create_and_name_Workbooks()
Dim file_path As String
Dim file_extension As String
Dim data_range As Range
Dim new_workbook As Workbook

file_path = "C:"           ' Change as required
file_extension = ".xlsm"    ' Change as required

Set data_range = Range("A1:A4")
For Each cell In data_range
Set new_workbook = Nothing
On Error Resume Next
Set new_workbook = Workbooks.Add()
On Error GoTo 0

If new_workbook Is Nothing Then
MsgBox "Error in creating a new workbook , please check", vbExclamation
Else
' 52 means the file is  xlOpenXMLWorkbookMacroEnabled
new_workbook.SaveAs Filename:=file_path & cell & file_extension, FileFormat:=52
End If
Next
End Sub

[/pre]
Narayan


Acknowledgements : http://blogs.office.com/b/microsoft-excel/archive/2009/07/07/use-the-vba-saveas-method-in-excel-2007.aspx

 

[kaushik03]

Narayan,


Thank you very much.


This is absolutely what I was looking for...


Once again thank you very much..


Kaushik