New book from sheet

[Guest]

Hi,


Can anyone help me please? i need to be able to create a new book from an
active sheet to save in a location specified by user. i.e: in workbook "
invoices" on sheet number 2865 ( this sheet is active ) press a button, and a
standard saveas dialog box appears. user can browse to location and save as
new book. but it has to be the 1 sheet only. i cannot do it.


Thanks,


N.S.

 

[Guest]

hi,
try somthing like this.....

sub SaveRange
ActiveSheet.UsedRange.Select
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Application.Dialogs(xlDialogSaveAs).Show
end sub

if you have a solid block of data you might use...
Activesheet.currentregion.select

Regards
FSt1

 

[Guest]

hi again,
I forgot to add.
this code should be placed in a standard module in a personal workbook. the
personal workbook should be saved to the xl start up folder then hidden.
on my pc it is c:\program files\office2k\office\XLStartup. it may have a
different path on your pc. this way the macro would be available to all
files. you can create an icon on a toolbar to run the macro quickly.
This would have to be done on each user's machine it make it available to
each user that needs/wants it.

regards
FSt1