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

Simple macro needed to export a worksheet
 

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 ToolsMacro 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<attarkon.ee
Arvi Laanemets


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

Simple macro needed to export a worksheet
 

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

Simple macro needed to export a worksheet
 

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

Simple macro needed to export a worksheet
 

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

Simple macro needed to export a worksheet
 

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

Simple macro needed to export a worksheet
 

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

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

Simple macro needed to export a worksheet
 

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

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

Simple macro needed to export a worksheet
 

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

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

Simple macro needed to export a worksheet
 

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

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