How to Copy worksheet to new workbook with dynamic name?

[Ashleigh K.]

Greetings All,

We're trying to create a macro to copy a worksheet to another workbook
instead of copying to the default name of 'Book1' it should copy to the new
workbook with the name of 'ExportDataMMDDYY where MMDDYY is the current
month day and year. The big questions are, 1) is it possible to have it
create the new workbook with a name such as that above, without first saving
the workbook? (just as book1.xls is the default book name that hasn't yet
been saved) and 2) how do I give the new workbook a name of
ExportDataMMDDYY?

At the risk of being redundant, here's a quick practical example. The source
workbook (SourceBook) will have 15 worksheet tabs. The user may run the
macro on any one of the tabs at a time. Each time the macro is run it
SHOULD copy the contents of the active worksheet into the new workbook with
the name ExportData123003 and then return to the SourceBook.

The code that I have managed to create so far is listed below. What I can't
figure out is how to have it name the new book with the file name format
indicated above. Any help would be HUGELY appreciated.

Thx, Ashleigh

---------My Sample Code Follows---------------
Sub GoatFeet()

CurrentBookName = ActiveWorkbook.Name

' Copy the active worksheet to a new workbook, ideally it would copy to a
workbook
' titled 'ExportDataMMDDYY where MMDDYY is the current month day and year

ActiveSheet.Copy

' Remove all formulas within the worksheet by copying and pasting only
values
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False

' Return to the original workbook.
'
Workbooks(CurrentBookName).Activate

 

[losmac]

Here You are:
Sub ExportDataToNewWrkBook()
Dim wbk As Workbook
Dim wsh As Worksheet
Dim sCell As String

Set wsh = ThisWorkbook.ActiveSheet
sCell = ActiveCell.Address
wsh.Cells.Select
wsh.Cells.Copy
Set wbk = Workbooks.Add
With wbk
.Worksheets(1).Range("A1").Select
.Worksheets(1).Paste
.SaveAs "D:\ExportData" & Format(Date, "mmddyy")
& ".xls"
End With
wsh.Activate
wsh.Range(sCell).Select

End Sub

-----Original Message-----
Greetings All,

We're trying to create a macro to copy a worksheet to another workbook
instead of copying to the default name of 'Book1' it should copy to the new
workbook with the name of 'ExportDataMMDDYY where MMDDYY is the current
month day and year. The big questions are, 1) is it possible to have it
create the new workbook with a name such as that above, without first saving
the workbook? (just as book1.xls is the default book name that hasn't yet
been saved) and 2) how do I give the new workbook a name of
ExportDataMMDDYY?

At the risk of being redundant, here's a quick practical example. The source
workbook (SourceBook) will have 15 worksheet tabs. The user may run the
macro on any one of the tabs at a time. Each time the macro is run it
SHOULD copy the contents of the active worksheet into the new workbook with
the name ExportData123003 and then return to the SourceBook.

The code that I have managed to create so far is listed below. What I can't
figure out is how to have it name the new book with the file name format
indicated above. Any help would be HUGELY appreciated.

Thx, Ashleigh

---------My Sample Code Follows---------------
Sub GoatFeet()

CurrentBookName = ActiveWorkbook.Name

' Copy the active worksheet to a new workbook, ideally it would copy to a
workbook
' titled 'ExportDataMMDDYY where MMDDYY is the current month day and year

ActiveSheet.Copy

' Remove all formulas within the worksheet by copying and pasting only
values
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,

Operation:=xlNone, SkipBlanks:=