Thanks for your help Stefan.

Turns out that they user did want the file saved after all, so I can just
revert back to my original code (typical).

"stefan onken" wrote:

hi,
change NewWorkbookName = NewWorkbook.Name
to NewWorkbookFunc = NewWorkbook.Name

another possibility is something like

Dim NewWorkbook
Function MakeWB()
Set NewWorkbook = Workbooks.Add
End Function
Function AccessNewWorkbook()
NewWorkbook.Sheets(1).Range("A1") = Time
End Function

If you need NewWorkbook in macros in different modules, use Public
instead of Dim (see VBA help also).

stefan

On 4 Sep., 12:10, Wullie wrote:
Okay, found my way around that problem, but now I would like my function to
return the newworkbookname as a string

I am calling the function like so

NewWorkbookName = NewWorkbookFunc(1)

and the function now looks like

Function NewWorkbookFunc(wsCount As Integer) As String
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long
Dim NewWorkbookName As String

Set NewWorkbook = Nothing
If wsCount < 1 Or wsCount 255 Then Exit Function
OriginalWorksheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = wsCount
Set NewWorkbook = Workbooks.Add()
NewWorkbookName = NewWorkbook.Name
Worksheets("Sheet1").Name = "Comparison"
Application.SheetsInNewWorkbook = OriginalWorksheetCount

End Function

but this is passing back an empty string value rather than the filename.

Any solutions?



"Wullie" wrote:
Hi,

Is there a way to create a new workbook and give it a name (for referencing)
without saving the file?

I currently use SaveAs, but don't really need to keep the file.

However, I do need to jump between files so need to be able to reference
this new workbook.

It's probably something simple that I have overlooked, but any help would be
greatly appreciated.

My function is currently

Function NewWorkbookFunc(wsCount As Integer) As Workbook
' creates a new workbook with wsCount (1 to 255) worksheets
Dim OriginalWorksheetCount As Long

Set NewWorkbook = Nothing
If wsCount < 1 Or wsCount 255 Then Exit Function
OriginalWorksheetCount = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = wsCount
Set NewWorkbook = Workbooks.Add
NewWorkbook.SaveAs Filename:=curPath & "\Comparison_" & Format(Date,
"yyyy_mm_dd")
Worksheets.Add().Name = "Comparison"
Application.SheetsInNewWorkbook = OriginalWorksheetCount

End Function

Can I just remove the SaveAs line and refer to NewWorkbook when activating
the new workbook?

Thanks- Zitierten Text ausblenden -

- Zitierten Text anzeigen -