Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell References | Excel Discussion (Misc queries) | |||
Setting a cell reference in one sheet that updates a workbook? | Excel Discussion (Misc queries) | |||
One workbook to another . . . | Excel Discussion (Misc queries) | |||
insert date | Excel Worksheet Functions | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) |