Save certain Worksheets to a NEW/BLANK Workbook Via the Browse to file window in VBA

[AZZAC01]

Hello all, I am a Newbie of a few months now to VBA. I have already wrote some macros to get data from a data base then manipulate and format it. Now at this point I would like the application to Save that Data in a new workbook where the user can specify the new name of the file and the path in the browse to folder window. That way every time the app is used were not saving all the code and extra stuff over and over, It just needs to be the spread sheets with the data only. I'm guesing it would use the TransferSpreadSheet Metod. . . Thanks in advance for any help.

[david000]

http://www.vbaexpress.com/forum/showthread.php?t=40113

This post might be very helpful

[AZZAC01]

Thanks for the response but unfortunately It looks like they are doing the same thing as a save as method. I actually would like to CREATE a New File(so it's without all The Code Modules that are in the current file) and paste only the values of the cells in my 5 different worksheets I have played around with it a bit but end up with the code modules in the new file. ( unnecessary memory usage) You see alot of these file are going to be generated. Thanks again. . . .

[AZZAC01]

Hello again. I was working on this and thought maybe I would narrow this down a bit. I read about the Workbook Object and came up with the code BELOW. It SUCCESSFULLY Creates a New File and Does a "Save As" using a Specified Directory Path And File Name. The Problem is I cant figure out how to Transfer my 5 Worksheets and their DATA ONLY with the TAB Names. What is the Syntax for the "WITH" Block that would allow me to do this??? Anyone.....? Thanks for your Help!

Dim NewWbPath As String
 NewWbPath = "V:\DB Change Notifications\"
Dim NewFileNm As String
 NewFileNm = Me!OpNewFileName.Text
 
Dim FileLocStr As String
FileLocStr = NewWbPath & NewFileNm & ".xls"
Set NewBook = Workbooks.Add
   With NewBook
      .Title = Me!OpNewFileName
      .SaveAs Filename:=FileLocStr
   End With

[david000]

Sub SaveSheet1()
 Dim i As Long
 Dim wb As Workbook
 Dim rng As Range
Sheets(Array("Sheet1", "Sheet2")).Copy 'Create new book and copy sheets
Set wb = ActiveWorkbook
    For i = 1 To wb.Sheets.Count
   With wb
      .Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value 'past that values
   End With
    Next
Application.Dialogs(xlDialogSaveAs).Show 'show the SaveAs dialog
End Sub

[msn1888]

david000