Hello everybody,

i have a excel workbook with different sheets.
I would like to copy from al these sheets certain cells (in tota
35cells) wich are interresting to me
( like for example data sheet 1 cell D24 - sheet2 Cell d1:d5 and f13 )

after the selection i would like to copy all these cells from thes
different sheets into a new excel file/workbook.
every cell data must be in a different row like A1,B1,C1,D1,E1,F1 ...

so i get 1 line with data?

is this possible can this be done ?

thank you for your time. :

--
captkiw
-----------------------------------------------------------------------
captkiwi's Profile: http://www.excelforum.com/member.php...fo&userid=1666
View this thread: http://www.excelforum.com/showthread.php?threadid=31873

You won't get very far with your question or your answers if you
get rows and columns mixed up. Rows: 1, 2, 3 Columns: A, B, C

What version of Excel do you have. With Excel 97 and up if you
chose multiple selections the cells can all be accessed as if they
were a single selection. The cells are returned to you left to
right and then left to right from the next row in a selection, etc.,
then the same for next selection.

Since there are only 256 columns, I will assume you did mean
rows as you said, even though your example ran across one
row changing the column.

Public Sub Sel_toSRows()
'Selection(s) to single rows on new sheet
'David McRitchie 2004-11-19
Dim nRow As Long, Cell As Range, rng As Range
Set rng = Selection
Sheets.Add After:=Sheets(Sheets.Count) '-- place at end
Set wsNew = ActiveSheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Cell In rng
nRow = nRow + 1
Cells(nRow, 1) = Cell.Value
Next Cell
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
i have a excel workbook with different sheets.
I would like to copy from al these sheets certain cells (in total
35cells) wich are interresting to me
( like for example data sheet 1 cell D24 - sheet2 Cell d1:d5 and f13 ).

after the selection i would like to copy all these cells from these
different sheets into a new excel file/workbook.
every cell data must be in a different row like A1,B1,C1,D1,E1,F1 ....

so i get 1 line with data?

Sorry I missed the part about multiple sheets being involved
with the selection. How do you intend to identify these sheets.

David McRitchie Wrote:
Sorry I missed the part about multiple sheets being involved
with the selection. How do you intend to identify these sheets.


Hello David,

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

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

--
captkiw
-----------------------------------------------------------------------
captkiwi's Profile: http://www.excelforum.com/member.php...fo&userid=1666
View this thread: http://www.excelforum.com/showthread.php?threadid=31873

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.

Originally was going to create another worksheet
until I saw you want another workbook instead. The upper
limit is really only a safety factor the ON ERROR is what
is supposed to stop looking at sheets.

change --
For i = 1 To (Sheets.Count - 1)
to --
For i = 1 To Sheets.Count

And in my first reply remove any reference to wsNew
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm