Public Sub Datax_NBSRows()
'Multiple Sheet Selection(s) to single rows on new Workbook
'David McRitchie 2004-11-19
Dim nRow As Long, Cell As Range, rng As Range, i As Integer
Dim vals(5000) As String
'cannot have different selections in grouped sheets
' this macro will cycle through sheets
' data1, data2,...,datan until missing sheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To (Sheets.Count - 1)
On Error Resume Next
Worksheets("data" & i).Activate
If Err.Number < 0 Then GoTo done
On Error GoTo 0
Set rng = Selection
For Each Cell In rng
nRow = nRow + 1
vals(nRow) = Cell.Value 'use cell.text for formatted value
Next Cell
Next i
done:
Workbooks.Add
Cells(1, 1) = "xxx"
For i = 1 To nRow
Cells(i, 1) = vals(i)
Next i
Cells.EntireColumn.AutoFit
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"captkiwi" wrote in message My sheets have names example data1, data2,data3.....dataX
there are about 35 cells in about 15 sheets wich are important to
transfer to a another new workbook.
most of them are cells with a formula. like: =SOM(N29:N33) but i just
need de value from this formula.

is there away you can select al these data and copy it to a new
workbook.
Many thnx for your time.