If you need more help post back
Bed time for me soon so here is small example to copy to a new workbook

Sub Test1()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

Application.ScreenUpdating = False
Set DestSh = Workbooks.Add.Worksheets(1)
For Each sh In ThisWorkbook.Worksheets
Last = LastRow(DestSh)

sh.Range("A1:C5").Copy DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below to copy only the values
'or use the PasteSpecial option to paste the format also.


'With sh.Range("A1:C5")
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.Range("A1:C5").Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

DestSh.Cells(Last + 1, "D").Value = sh.Name
'This will copy the sheet name in the D column if you want

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
See
http://www.rondebruin.nl/copy2.htm

I use a master sheet in the same workbook but you can also change it to put the info in a new workbook
If you need more help post back


--
Regards Ron de Bruin
http://www.rondebruin.nl


"dreamkeeper" wrote in message oups.com...
Background: Hi! I have built a very big reporting and forecasting tool
that has over 30 worksheets tht link and roll into each other.

The report is HUGE and we have no central repository to share this tool
plus only a couploe of people need to actually use the tool to
forecast. Mainly, we want people to see the end results.
Question:Is there a way to export the values of each sheet (30 of them)
at one time into an empty shell of a different work book so we can use
that one to distribute?

So basiclaly, I am looking to write a macro to export the value and
formats (no formulas) of many sheets into a new workbook without having
to copy and paste one sheet at a time.

thanks for your amazing help!
Tina