VBA code to save data

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote