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" <(E-Mail Removed)> wrote in message
news:3D84D2B9-936E-41A6-8AC6-(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in message
>> news:BB0CB772-EDDA-43F3-8463-(E-Mail Removed)...
>> > 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.
>>
>>
>>
Similar Threads
