Simple macro needed to export a worksheet

Hi all

I need a simple macro to export one worksheet, including formulae, values
and all formating, to a new workbook. Can anyone help me?

Rob

Hi

Right-click on sheet tab, select 'Move or Copy...', into 'To book' field
select '(new book)', check 'Create a copy', and press OK.

When you want a macro, switch 'Record new Macro' from Tools>Macro menu
before. When finished, stop the recording, and probably you have to edit it
afterwards to ensure it works from any active sheet {replace any part of
code containing something like 'Sheets("YourSheetName")' with 'ActiveSheet',
etc.}

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"RobG" <[email protected]> wrote in message
news:[email protected]...
> Hi all
>
> I need a simple macro to export one worksheet, including formulae, values
> and all formating, to a new workbook. Can anyone help me?
>
> Rob

This will do the trick:

Sub CopySheet()
Sheets("Sheet1").Copy ' copies Sheet1 to a new workbook
End Sub

--
Ole P.

RobG wrote:
> Hi all
>
> I need a simple macro to export one worksheet, including formulae, values
> and all formating, to a new workbook. Can anyone help me?
>
> Rob

Here is some code that will do everything up until providing a Save As window
for you to enter the filename:

Sub expsheet()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName
End Sub

Andrea Jones

"RobG" wrote:

> Hi all
>
> I need a simple macro to export one worksheet, including formulae, values
> and all formating, to a new workbook. Can anyone help me?
>
> Rob

Thanks for that, I knew it should be simple!

Rob

"Ole P. Erlandsen" wrote:

> This will do the trick:
>
> Sub CopySheet()
> Sheets("Sheet1").Copy ' copies Sheet1 to a new workbook
> End Sub
>
> --
> Ole P.
>
> RobG wrote:
> > Hi all
> >
> > I need a simple macro to export one worksheet, including formulae, values
> > and all formating, to a new workbook. Can anyone help me?
> >
> > Rob
>

Andrea

This works great. In the Save As window it only gives me the option of All
Files in the Save as type box. Is there a way of getting this to save as
workbook, or am I trying to be too clever?

Regards

Rob

"Andrea Jones" wrote:

> Here is some code that will do everything up until providing a Save As window
> for you to enter the filename:
>
> Sub expsheet()
> Sheets("Sheet1").Select
> Sheets("Sheet1").Copy
> Do
> fName = Application.GetSaveAsFilename
> Loop Until fName <> False
> ActiveWorkbook.SaveAs Filename:=fName
> End Sub
>
> Andrea Jones
>
> "RobG" wrote:
>
> > Hi all
> >
> > I need a simple macro to export one worksheet, including formulae, values
> > and all formating, to a new workbook. Can anyone help me?
> >
> > Rob

The dialog doesn't do the saving.

This command does:

ActiveWorkbook.SaveAs Filename:=fName

It should default to workbook, but to be absolutely sure

Do
fName = Application.GetSaveAsFilename( _
FileFilter:="Workbook Files (*.xls), *.xls"
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName, _
FileFormat :=xlWorkbookNormal

--
Regards,
Tom Ogilvy


"RobG" <[email protected]> wrote in message
news:[email protected]...
>
> Andrea
>
> This works great. In the Save As window it only gives me the option of All
> Files in the Save as type box. Is there a way of getting this to save as
> workbook, or am I trying to be too clever?
>
> Regards
>
> Rob
>
> "Andrea Jones" wrote:
>
> > Here is some code that will do everything up until providing a Save As
window
> > for you to enter the filename:
> >
> > Sub expsheet()
> > Sheets("Sheet1").Select
> > Sheets("Sheet1").Copy
> > Do
> > fName = Application.GetSaveAsFilename
> > Loop Until fName <> False
> > ActiveWorkbook.SaveAs Filename:=fName
> > End Sub
> >
> > Andrea Jones
> >
> > "RobG" wrote:
> >
> > > Hi all
> > >
> > > I need a simple macro to export one worksheet, including formulae,
values
> > > and all formating, to a new workbook. Can anyone help me?
> > >
> > > Rob

Tom

I can't get this to work - I keep getting error messages about syntax errors
or Compile error: Expected: expression. Any idea what I am doing wrong?

Rob

"Tom Ogilvy" wrote:

> The dialog doesn't do the saving.
>
> This command does:
>
> ActiveWorkbook.SaveAs Filename:=fName
>
> It should default to workbook, but to be absolutely sure
>
> Do
> fName = Application.GetSaveAsFilename( _
> FileFilter:="Workbook Files (*.xls), *.xls"
> Loop Until fName <> False
> ActiveWorkbook.SaveAs Filename:=fName, _
> FileFormat :=xlWorkbookNormal
>
> --
> Regards,
> Tom Ogilvy
>
>
> "RobG" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Andrea
> >
> > This works great. In the Save As window it only gives me the option of All
> > Files in the Save as type box. Is there a way of getting this to save as
> > workbook, or am I trying to be too clever?
> >
> > Regards
> >
> > Rob
> >
> > "Andrea Jones" wrote:
> >
> > > Here is some code that will do everything up until providing a Save As
> window
> > > for you to enter the filename:
> > >
> > > Sub expsheet()
> > > Sheets("Sheet1").Select
> > > Sheets("Sheet1").Copy
> > > Do
> > > fName = Application.GetSaveAsFilename
> > > Loop Until fName <> False
> > > ActiveWorkbook.SaveAs Filename:=fName
> > > End Sub
> > >
> > > Andrea Jones
> > >
> > > "RobG" wrote:
> > >
> > > > Hi all
> > > >
> > > > I need a simple macro to export one worksheet, including formulae,
> values
> > > > and all formating, to a new workbook. Can anyone help me?
> > > >
> > > > Rob
>
>
>

There was a missing close paren on the fname statement.

I tested this and it worked fine:

Sub AA()
Do
fName = Application.GetSaveAsFilename( _
FileFilter:="Workbook Files (*.xls), *.xls")
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName, _
FileFormat:=xlWorkbookNormal

End Sub

--
Regards,
Tom Ogilvy

"RobG" <[email protected]> wrote in message
news:[email protected]...
> Tom
>
> I can't get this to work - I keep getting error messages about syntax
errors
> or Compile error: Expected: expression. Any idea what I am doing wrong?
>
> Rob
>
> "Tom Ogilvy" wrote:
>
> > The dialog doesn't do the saving.
> >
> > This command does:
> >
> > ActiveWorkbook.SaveAs Filename:=fName
> >
> > It should default to workbook, but to be absolutely sure
> >
> > Do
> > fName = Application.GetSaveAsFilename( _
> > FileFilter:="Workbook Files (*.xls), *.xls"
> > Loop Until fName <> False
> > ActiveWorkbook.SaveAs Filename:=fName, _
> > FileFormat :=xlWorkbookNormal
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "RobG" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > Andrea
> > >
> > > This works great. In the Save As window it only gives me the option of
All
> > > Files in the Save as type box. Is there a way of getting this to save
as
> > > workbook, or am I trying to be too clever?
> > >
> > > Regards
> > >
> > > Rob
> > >
> > > "Andrea Jones" wrote:
> > >
> > > > Here is some code that will do everything up until providing a Save
As
> > window
> > > > for you to enter the filename:
> > > >
> > > > Sub expsheet()
> > > > Sheets("Sheet1").Select
> > > > Sheets("Sheet1").Copy
> > > > Do
> > > > fName = Application.GetSaveAsFilename
> > > > Loop Until fName <> False
> > > > ActiveWorkbook.SaveAs Filename:=fName
> > > > End Sub
> > > >
> > > > Andrea Jones
> > > >
> > > > "RobG" wrote:
> > > >
> > > > > Hi all
> > > > >
> > > > > I need a simple macro to export one worksheet, including formulae,
> > values
> > > > > and all formating, to a new workbook. Can anyone help me?
> > > > >
> > > > > Rob
> >
> >
> >

Tom

Works like a dream - thanks!

Rob

"Tom Ogilvy" wrote:

> There was a missing close paren on the fname statement.
>
> I tested this and it worked fine:
>
> Sub AA()
> Do
> fName = Application.GetSaveAsFilename( _
> FileFilter:="Workbook Files (*.xls), *.xls")
> Loop Until fName <> False
> ActiveWorkbook.SaveAs Filename:=fName, _
> FileFormat:=xlWorkbookNormal
>
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "RobG" <[email protected]> wrote in message
> news:[email protected]...
> > Tom
> >
> > I can't get this to work - I keep getting error messages about syntax
> errors
> > or Compile error: Expected: expression. Any idea what I am doing wrong?
> >
> > Rob
> >
> > "Tom Ogilvy" wrote:
> >
> > > The dialog doesn't do the saving.
> > >
> > > This command does:
> > >
> > > ActiveWorkbook.SaveAs Filename:=fName
> > >
> > > It should default to workbook, but to be absolutely sure
> > >
> > > Do
> > > fName = Application.GetSaveAsFilename( _
> > > FileFilter:="Workbook Files (*.xls), *.xls"
> > > Loop Until fName <> False
> > > ActiveWorkbook.SaveAs Filename:=fName, _
> > > FileFormat :=xlWorkbookNormal
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "RobG" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > Andrea
> > > >
> > > > This works great. In the Save As window it only gives me the option of
> All
> > > > Files in the Save as type box. Is there a way of getting this to save
> as
> > > > workbook, or am I trying to be too clever?
> > > >
> > > > Regards
> > > >
> > > > Rob
> > > >
> > > > "Andrea Jones" wrote:
> > > >
> > > > > Here is some code that will do everything up until providing a Save
> As
> > > window
> > > > > for you to enter the filename:
> > > > >
> > > > > Sub expsheet()
> > > > > Sheets("Sheet1").Select
> > > > > Sheets("Sheet1").Copy
> > > > > Do
> > > > > fName = Application.GetSaveAsFilename
> > > > > Loop Until fName <> False
> > > > > ActiveWorkbook.SaveAs Filename:=fName
> > > > > End Sub
> > > > >
> > > > > Andrea Jones
> > > > >
> > > > > "RobG" wrote:
> > > > >
> > > > > > Hi all
> > > > > >
> > > > > > I need a simple macro to export one worksheet, including formulae,
> > > values
> > > > > > and all formating, to a new workbook. Can anyone help me?
> > > > > >
> > > > > > Rob
> > >
> > >
> > >
>
>
>

when i run the macro, i get a error message:
compile error:
can't find project or library
and the "fName" is highlighted

help me please
thank you all