using a cell value to name a newly created workbook
 

Hello,

I want to use the value in a specific cell to appear as part of the
name of a newly created workbook, which is created following input into
the original workbook. i.e. the user enters the name of a company and
when the macro is run, the name of the company appears as the filename
of the workbook and the workbook is saved to the users desktop,

how would I go about this?

regards,

Matt

using a cell value to name a newly created workbook
 

Matt:

Try this one. There are two options depending on if it is a new workbook or
the current one.

Option Explicit

Sub nameworkbookondesktop()

'assume the cell is B1

Dim szFilename As String
Dim szDesktop As String
Dim wb As Workbook
Dim objWSHShell As Object

Set objWSHShell = CreateObject("WScript.Shell")
szDesktop = objWSHShell.SpecialFolders("Desktop") & "\"

szFilename = ActiveSheet.Range("B1")

'if a new workbook
Set wb = Workbooks.Add

'if the current workbook
'Set wb = ActiveWorkbook

' save it
wb.SaveAs szDesktop & szFilename
'if you want to close it afterwards
wb.Close True

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Guerilla" wrote:

Hello,

I want to use the value in a specific cell to appear as part of the
name of a newly created workbook, which is created following input into
the original workbook. i.e. the user enters the name of a company and
when the macro is run, the name of the company appears as the filename
of the workbook and the workbook is saved to the users desktop,

how would I go about this?

regards,

Matt