Copy Worksheet to new Workbook and naming

[Guest]

Good evening, All. I'm trying to set up a macro that I can use a radio
button for.

The Macro would take a worksheet entitled "Summary Sheet" and copy values
and formats to a new workbook. I'd like to have the new workbook named based
upon the concantination of values in cells B4 and B6 on the source worksheet,
such as 500_200707. I've searched this site, but didn't find what I needed
(or didn't seach with the right phrasing. Any thoughts?

I believe I can step through a macro to do most of this, but especially do
not understand how to name the new workbook in the macro of the source
workbook. Thanks for any advise or reference. -Bill E.

 

[Gary Keramidas]

something like this may get you started. kind of late here.

Dim fPath As String
Dim fName As String
Dim ws As Worksheet
Set ws = Worksheets("Summary Sheet")
fPath = ThisWorkbook.Path & "\"
fName = ws.Range("B4").Value & ws.Range("B6").Value & ".xls"

ActiveWorkbook.SaveAs Filename:=fPath & fName

 

[Guest]

Many thanks, Gary! It works well, except I'd like to only have the "Summary
Sheet" copied over, not the whole workbook. Can that be done only keeping
the "Summary Sheet" worksheet values and format in the new workbook? I do
not want any links to the source workbook.

I really need to take a class in this! -Bill

 

[Gary Keramidas]

bill:

this is one way, see how this works for you.


Sub Copy_Summary()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim fPath As String
Dim fName As String

Set ws = Worksheets("Summary Sheet")
Set ws2 = Worksheets.Add(after:=Worksheets(Worksheets.Count))
fPath = ThisWorkbook.Path & "\"
fName = ws.Range("B4").Value & ws.Range("B6").Value & ".xls"

Application.ScreenUpdating = False
ws.Cells.Copy
With ws2.Range("A1")
.PasteSpecial (xlPasteValues)
.PasteSpecial (xlPasteFormats)
End With

Application.DisplayAlerts = False
Worksheets(Worksheets.Count).Select
ActiveWindow.SelectedSheets.Copy
ActiveWorkbook.SaveAs Filename:=fPath & fName
ActiveWorkbook.Close
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
MsgBox fName & " saved in " & fPath
Application.ScreenUpdating = True
End Sub

 

[Guest]

Awsome, Gary! Thanks for the help. If you get to San Francisco, I'll
ante-up for a pint of Guinness. -Bill