Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple Macro needed | New Users to Excel | |||
how do you export excel files to Quickbooks Simple edition | New Users to Excel | |||
Macro needed to pull data from one worksheet and enter it in anoth | Excel Discussion (Misc queries) | |||
Needed: worksheet export help | Excel Discussion (Misc queries) | |||
Export simple appointments from Excel to Outlook | Excel Worksheet Functions |