Copy Worksheet to new Workbook and naming
 

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.

Copy Worksheet to new Workbook and naming
 

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

--


Gary


"Bill E" <Bill wrote in message
...
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.

Copy Worksheet to new Workbook and naming
 

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" wrote:

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

--


Gary


"Bill E" <Bill wrote in message
...
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.

Copy Worksheet to new Workbook and naming
 

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



--


Gary


"Bill E" wrote in message
...
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" wrote:

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

--


Gary


"Bill E" <Bill wrote in message
...
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.

Copy Worksheet to new Workbook and naming
 

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

"Gary Keramidas" wrote:


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



--


Gary


"Bill E" wrote in message
...
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" wrote:

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

--


Gary


"Bill E" <Bill wrote in message
...
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.