Excel 2010 VBA: How to export the current worksheet asking user where to save? -

i've .xlsm big customized toolbar full of custom tools handling common situations @ work.

the problem need save result , send other users, don't want preserve macros / customized toolbars saved workbook.

so i'm trying way: add workbook, copy/paste actual worksheet new workbook, i'd force opening of save as.

'copy entire sheet cells.copy range("b2").select 'just leave 1 cell selected when return   'paste new workbook sheet 1 workbooks.add cells.select application.displayalerts = false activesheet.paste application.displayalerts = true activesheet.range("b2").select 'select 1 cell application.filedialog(msofiledialogsaveas)     .show end 

the problem after write file name , choose destination folder , press 'save', i got nothing saved ! no file created @ all.

edit 1

probably i'm not clear in intention. i've .xlsm use import , manipulate date other sources. got sheet data need (and contains no formulas). need create new workbook, copy/paste sheet new file, force opening of save as. @ point i manually select destination folder, enter file name , choose file format, , press save. need able choose these things @ runtime

actually problem excel doesn't save new file , don't know why.

why?

give go, 1 more time...

      sub saveasdibox()     dim flsv variant     dim myfile string     dim sh worksheet      set sh = sheets("sheet1")     sh.copy     myfile = "yourfilename.xlsx"     flsv = application.getsaveasfilename(myfile, filefilter:="excel files (*.xlsx), *.xlsx)", title:="enter file name")      if flsv = false exit sub      myfile = flsv      activeworkbook         .saveas (myfile), fileformat:=51, createbackup:=false         .close false     end  end sub