Excel/Macro for



  It's been a while but I know you are the guy to ask for this macro-related question.

  I need a macro that will copy a named range (its a table of calculated values) to a user-specified location with a user-specified file name.

  I want ONLY the table to be saved as a file, not the other sheets in the workbook which helped calculate that table.

  The table to be copied is largely calculated results so it must be copied as values, since the other pages won't be present in the saved file.

   Furthermore, the saved file type must be a CSV type.

   The table is dynamic and must be referred to by its dynamic filename, which is DynOutputTable

   I envision bringing up File-->Save As, allowing the user to specify the file name and the output location, as well as presetting up the CSV file type (so they don't have to choose the CSV type manually).

  An alternate version could be to save the file as a predefined name with the week-ending date embedded in it, (Example: TimeCard061016.csv) yet still alllowing the user to select the destination folder each time they save.

  Perhaps there is an intermediate step where the table in question is saved out to a new single-sheet workbook with a temporary name, and it is that temporary workbook which would be Saved-As.  That would be okay with me if needed.

   Hoping you can easily knock this out (it doesn't seem too complex but is beyond my macro-writing abilities) and it isn't a difficult problem,

   >>>> Bill thanking you in advance!


this code worked for me.  I assume the Sheet with the table will be active when the macro is run.

Sub CopyTable()
Dim r As Range, bk As Workbook
Dim sh As Worksheet, fname As Variant
Set r = ActiveSheet.ListObjects("DynOutputTable").Range
Workbooks.Add Template:=xlWBATWorksheet
Set bk = ActiveWorkbook
Set sh = ActiveSheet
sh.Range("A1").PasteSpecial xlValues
fname = Application.GetSaveAsFilename(InitialFileName:="timecard.csv", _
       FileFilter:="CSV Files (*.csv),*.csv", _
        Title:="Select File Name")
If fname = False Then
 MsgBox "Invalid name selection, quitting"
 bk.Close SaveChanges:=False
 Set bk = Nothing
 If LCase(Right(fname, 4)) <> ".csv" Then
   fname = fname & ".csv"
 End If
 bk.SaveAs Filename:=fname, FileFormat:=xlCSV
 bk.Close SaveChanges:=False
 Set bk = Nothing
End If
End Sub

Tom Ogilvy

---------- FOLLOW-UP ----------


  Thanks for the incredibly-fast answer!

  I am having a problem with it.  The macro blows out at line

     Set r = ActiveSheet.ListObjects("DynOutputTable").Range

    The macro blowout message is "Runtime error '9'; subscript out of range"

  DynOutputTable is a dynamic range created by an OFFSET function, as follows

         =OFFSET(Translator!$A$1,0,0,'Hidden Sheet'!$E$4,3)

   where 'Hidden Sheet'!$E$4 contains a COUNTA of one column in the table.  

  When I enter DynOutputTable in the Name Box, the proper table is highlighted.

  Maybe the macro doesn't recognize dynamic table names?

   >>> Bill


So a listobject is an Excel Table, but it sounds like you just have a named range (which is dynamic).  So I would replace

Set r = ActiveSheet.ListObjects("DynOutputTable").Range


Set r = Thisworkbook.Names("DynOutputTable").ReferstoRange

I would expect that to work if it is in fact a named range.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 About.com. All rights reserved.