Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: how to save worksheets in another excel file?

  1. #1

    how to save worksheets in another excel file?

    I used these VB codes to save my worksheet in another excel file. How can I save more than one sheet?
    [VBA]Private Sub CmdSave_Click()
    Dim CurWkbook As Workbook
    Dim SheetToSave As Worksheet
    Dim newWkbook As Workbook
    Dim sFileName As String

    'Show the SaveAs dialog. sFileName is the path to save to
    sFileName = Application.GetSaveAsFilename(InitialFileName:="", fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save mySheet to..")

    'User didn't choose to save, we exit
    If sFileName = "False" Then Exit Sub

    'Prevent screen from changing
    Application.ScreenUpdating = False

    Set CurWkbook = Application.ActiveWorkbook
    Set SheetToSave = CurWkbook.Sheets(2) 'Maryam: select other sheets to save other sheets

    'Create a new workbook and copy SheetToSave to new workbook
    Workbooks.Add (xlWBATWorksheet)
    Set newWkbook = ActiveWorkbook
    newWkbook.SaveAs Filename:=sFileName
    SheetToSave.Copy Before:=newWkbook.Sheets(1)

    'Save and close new workbook
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    'Allow screen updating
    Application.ScreenUpdating = True

    'Inform user sheet has been saved
    MsgBox Prompt:=SheetToSave.Name & " saved to " & sFileName, _
    Buttons:=vbOKOnly + vbInformation, _
    Title:="Sheet Saved"
    End Sub
    Private Sub CommandButton2_Click()
    FrmComponent.Show
    End Sub[/VBA]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    If you do it in Excel with the macro recorder switched on, you will get the code to do this.

  3. #3
    these codes are in VBA in Excel. What is macro recorder? How to put it on? Would you pls explain more?

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Tools/Macros/Record new macro.
    Carry out the required actions then Stop Recording.
    You can view/edit your recorded code in the VBE.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    What do you actually want to copy?

    The following will copy Sheet1 and Sheet2 to a new workbook and save it with the name NewWorkbook.
    Sheets(Array("Sheet1", "Sheet2")).Copy
    
    Set wb = ActiveWorkbook
    
    wb.SaveAs ThisWorkbook.Path & "\NewWorkbook.xls"
    
    wb.Close

  6. #6
    I cannot find view/ edit. what can we save with macro recorder? how does it work?

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    View and edit are terms Malcolm was using, they are not buttons.

    If you record a macro, you can then go into the VBIDE (Alt-F11), and see the code that you have just created (view), and change it (edit).

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Look for "Create a macro" in Excel help
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    Dear Xld and Malcolm now I know what is macro recorder, but I cannot find the suitable codes for doing this job. I want to save two sheets into another workbook, but I want the standard save form to open and the user define the path. Now post#1 does this for saving one sheet . I cannot change this Set SheetToSave = CurWkbook.Sheets(2) to Set SheetToSave= CurWkbook.Sheets(Array("Sheet1", "Sheet2")). It gives Debug. So how to change the codes?

  10. #10
    I also changed Dim SheetToSave As Worksheets instead of Worksheet but still it gives mismatch type error.

  11. #11
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    maram,
    I can offer just a hint here....if you wish to use this:
    [vba]'Show the SaveAs dialog. sFileName is the path to save to
    sFileName = Application.GetSaveAsFilename(InitialFileName:="", _
    fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save mySheet to..")
    [/vba]
    I want the standard save form to open and the user define the path.
    then you might as well run it twice and just set your
    [vba]Set SheetToSave = CurWkbook.Sheets(2)[/vba]
    to
    [vba]Set SheetToSave = CurWkbook.ActiveSheet[/vba]
    and run it on each one while they are active.....

    If you wish to do them at the same time you can set it up to activate each sheet you wish to run it on then use the activesheet code or you could use the array that Norie has offered but you will have to name them in the code...

    I'm sure there will be better suggestions....
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  12. #12
    I want to save both sheets together. I tried array It means i did these changes to my codes (#1) but it gives mismatch type debug:
    Dim SheetToSave As Worksheets instead of Dim SheetToSave As Worksheet
    Set SheetToSave = CurWkbook.sheets(Array("Sheet1", "Sheet2"))

  13. #13
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    do you wish to use the saveas dialog for them to name the new workbooks?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  14. #14
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Why do you have that code?

    Did you try the code I posted at all?

  15. #15
    Dear Norie,
    i also want the standard save window to open and to the user select where to save the file.

  16. #16
    Norie that will open the new workbook in the same file. then it will make my file huge and it takes time every time to open or save.

  17. #17
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    What about this one ...
    [VBA]'File to save to
    Dim sFile
    'current workbook
    Dim cWB As Workbook
    'destination workbook
    Dim oWB As Workbook
    'worksheets in cWB
    Dim oWS As Worksheet
    'destination worksheet
    Dim dWS As Worksheet
    'current workbook current sheet range
    Dim oWSr As Range
    'destination workbook sheet to copy to range
    Dim dWSr As Range
    'declare cWB as the workbook to start with
    Set cWB = ActiveWorkbook
    'choose file to save to
    sFile = Application.GetSaveAsFilename(InitialFileName:="", _
    fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save mySheet to..")
    'sFile = Application.GetOpenFilename("Excel files (*.xls), *.xls")
    If sFile <> False Then
    Set oWB = Workbooks.Add
    With oWB
    For Each oWS In cWB.Worksheets
    'create sheets of source in destination and select range
    Set oWSr = cWB.Worksheets(oWS.Name).Range("A1:B1")
    'add worksheet in newly created workbook and name it the same
    .Worksheets.Add(after:=.Worksheets(.Worksheets.Count)).Name = oWS.Name
    'create destination range to copy to
    Set dWSr = .Worksheets(oWS.Name).Range("A1:B1")
    'do the copy
    oWSr.Copy dWSr
    'next sheet in cWB if there is one
    Next oWS
    'save as chosen filename
    .SaveAs Filename:=sFile
    End With
    End If[/VBA]Charlize

  18. #18
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Quote Originally Posted by maryam
    Norie that will open the new workbook in the same file. then it will make my file huge and it takes time every time to open or save.
    Eh, no it won't.

  19. #19
    Norie is right, it doesn't. Thanks for the code Norie...just happened to stumble across this thread the other day and it was exactly what i was looking for! Now, do you know how to delete the "newworkbook". I am using this to return a survey in email and i would like to delete the new workbook once the email is sent.

  20. #20
    charlize it gives me debug that :"cannot rename the sheet to the same name as another sheet, a refrenced object library or..."
    Dear Norie I want to save it in a seprate file still.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •