Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Tullahoma, Tennessee, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA code to save data

    What VBA code would help me save a range or a named range of data to a new workbook, prompting for a filename. And another macro to do the opposite... prompt them for the file to open, and then copy the named range back into the current sheet?
    Thank you!

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code to save data

    In response to your first question, the following code saves the range Test from the active workbook to a new workbook for which the user is prompted for a name (default Test.xls):

    Sub test()
    Dim r As Range, strName As String
    Set r = Range("Test") 'get a handle on the range you want to copy
    Workbooks.Add 'add a new workbook
    r.Copy 'copy the range
    ActiveSheet.Paste 'paste it to the new workbook

    strName = Application.GetSaveAsFilename("Test.xls", "Excel Files (*.xls),*.xls,All Files,*.*") 'get a file name
    If strName <> False Then 'if the user didn't press cancel
    ActiveWorkbook.Save strName 'Save the new file
    End If
    ActiveWorkbook.Close 'Close the new file
    End Sub

    For question 2, try the following:
    Sub test2()
    Dim w As Worksheet, strName As String
    Set w = ActiveSheet 'Get a handle on the current sheet
    strName = Application.GetOpenFilename("Excel Files (*.xls),*.xls,All Files,*.*") 'Get the name of the file to open
    If strName <> "False" Then 'if the user didn't press cancel
    Workbooks.Open strName 'Open the file
    End If
    Range("Test").Copy 'Copy the range
    w.Paste 'Paste the range to the original worksheet
    End Sub

    HTH

    Jon

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Tullahoma, Tennessee, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code to save data

    I cannot thank you enough! It worked great and I so appreciate the help.

Posting Permissions

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