Macro to create a second excel workbook and copy a sheet?

B

bobbly_bob

Alrighty, what I have is an original workbook that runs a heap of
different IFs and LOOKUPs and fills out a template.

The filled out template has to then be emailed off to people, but
without them having a copy of that original workbook that fills it out.

I figure the easiest way would be for a macro to create a new workbook,
with a complete copy of the values from that particular sheet with the
completed template.

Ideally, I'd love it if that created workbook was able to pull its name
from a particular cell that contains the name of the client.

I've mucked around with few macros but only very basic, anyone able to
lend a hand?

cheers,
 
J

Jon von der Heyden

Hi,

Try this, assumes name of new file is in cell A1. And you may also want to
change the directory that it is saved to.

Sub Macro()
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\" & Range("A1").Value & ".xls"
End Sub
 
Top