Making a button in an Excel Spreadsheet to export to another

[Jon Arbuckle]

Hello:

I have a spreadsheet that is pretty much static with the exception of
approximately 15 fields. Changing those 15 fields will change the entire
sheet (and several pages within the sheet). I would like to create a button
on my Excel spreadsheet that will simply export all of the numerical data
(not the formulas, but just the number in the field at that time),
formatting, and pages to a completely different spreadsheet (a brand new
workbook that the user will need to save upon exiting).

Is this possible, and how would I do it?

Thanks in advance for any help provided!

 

[Nick Hodge]

Jon

This should get you going. (You will need to change the name of the original
sheet and the range that the data is in. You should be able to see that in
the code. You then need to place this in a standard module in the
originating workbook. It will copy the sheet to a new workbook and
copy>pastespecial... the values and formats. neither book is saved or close
in the code

Sub MoveSheetAndKillFormula()
Dim currwks As Worksheet
Dim wb As Workbook
Dim newwks As Worksheet

Set currwks = ThisWorkbook.Worksheets("DataSheet")

currwks.Copy
Set wb = ActiveWorkbook
Set newwks = wb.ActiveSheet

With newwks.Range("A1:Z100")
.Copy
.PasteSpecial xlPasteValuesAndNumberFormats
End With
End Sub

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog