automate copy and paste whole workbook to new workbook

I have a "destination" workbook that is linked to the "main" workbook using
SumProduct and Match. The problem is I can't email the destination workbook
to anyone who doesn't have the main workbook because they can't read it. Is
there some programming I could do that says paste values and formatting of
the destination workbook to another workbook and automatically save it using
the sheet name as the filename (or override if filename already exists) ? If
I can do this then I don't have to worry that no one has the main workbook.

Hey leslie,

If you want to copy a sheet from one workbook to another workbook and
save the destination workbook, you can easily use the macro recorder.
Think this is how it should look like.

Sheets("Sheet1").Select
Sheets("Sheet1").Copy
Before:=Workbooks("destination.xls").Sheets(1)
wkbk.Close SaveChanges:=True
MsgBox ("DONE!")

Greetz

Thank you I will give it a shot. What if I have multiple sheets in a
workbook, is there a macro that will copy and save the whole workbook. Also,
when I do this macro is it saving the formulas or the values. If it creates a
new workbook with the formulas I still have the problem of no one being able
to read the document because they don't have the source document. Thanks.

"[email protected]" wrote:

> Hey leslie,
>
> If you want to copy a sheet from one workbook to another workbook and
> save the destination workbook, you can easily use the macro recorder.
> Think this is how it should look like.
>
> Sheets("Sheet1").Select
> Sheets("Sheet1").Copy
> Before:=Workbooks("destination.xls").Sheets(1)
> wkbk.Close SaveChanges:=True
> MsgBox ("DONE!")
>
> Greetz
>
>

Leslie

It won't create a new workbook, and it will copy all your formulas with
it.
When you copy all your relevant sheets to your source workbook, i think
it will work.

CN

Hi Leslie,

Here's a routine that I've been using for quite a few years that will copy a
single sheet from a workbook, values only, and then prompt the user (i.e.
you!) to save the static, values only worksheet as a new workbook instance.
In that way, you can freely distribute the new static, values only workbook
and not have to worry about any linking back to source workbook issues.

Sub RangeValue()
WSToExport = Application.InputBox(Prompt:="Enter Name of Worksheet to
Export:", _
Title:="Export Worksheet (Range Valued)", Default:=ActiveSheet.Name,
Type:=2)
' What if User clicks 'Cancel'?
If VarType(WSToExport) = vbBoolean And WSToExport = False Then Exit Sub
OrigWbName = Worksheets(WSToExport).Parent.Name
With Workbooks(OrigWbName).Worksheets(WSToExport)
.Copy
.UsedRange.Copy
End With
ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select
ExportFileName =
Application.GetSaveAsFilename(InitialFileName:=WSToExport,
FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
If ExportFileName <> False Then ActiveWorkbook.SaveAs
FileName:=ExportFileName, FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close SaveChanges:=False
End Sub

Of course you can wrap the relevant lines of code above in a For Each ...
Next block to loop through each worksheet of the workbook in question. Some
further tweaks probably required in order to fit your specific purpose, but I
hope that gives you the general idea and a starting point.

HTH, Sean.

"Leslie" wrote:

> Thank you I will give it a shot. What if I have multiple sheets in a
> workbook, is there a macro that will copy and save the whole workbook. Also,
> when I do this macro is it saving the formulas or the values. If it creates a
> new workbook with the formulas I still have the problem of no one being able
> to read the document because they don't have the source document. Thanks.
>
> "[email protected]" wrote:
>
> > Hey leslie,
> >
> > If you want to copy a sheet from one workbook to another workbook and
> > save the destination workbook, you can easily use the macro recorder.
> > Think this is how it should look like.
> >
> > Sheets("Sheet1").Select
> > Sheets("Sheet1").Copy
> > Before:=Workbooks("destination.xls").Sheets(1)
> > wkbk.Close SaveChanges:=True
> > MsgBox ("DONE!")
> >
> > Greetz
> >
> >