copy certain cells to a new excelfile/workbook

[captkiwi]

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. :

 

[David McRitchie]

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

 

[David McRitchie]

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

 

[captkiwi]

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

 

[David McRitchie]

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

 

[David McRitchie]

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

 

[captkiwi]

David,
thank you very much for you time

I copied and paste this code in my workbook as a module
When i run your vb script it copies xxx in a new workbook, at cel
position 1,1.
The problem is how do i define my range sheets and cells for ever
sheet.
and copy that to a new excel file.

Iam sorry but iam really new to this Excel programming and it saves m
a lot of work everyday to sort these data out.



MY PROBLEM DESCRIPTIO
i have a excel workbook with different sheets.

My sheets have names example data1, test2,analyse3.....nameX

there are about 35 cells in about 15 sheets wich are important t
transfer to a another new excelFile.


I would like to copy from al these sheets certain cells (in tota
35cells) wich are interresting to me
( like for example data

sheet1 cell D24 and F23
sheet2 Cell D15 and F13
sheet3 Cell b1 and G13 and H7
SheetX Cell ........
most of them are cells with a formula. like: =SOM(N29:N33) but i jus
need de VALUE from this formula.

after the selection i would like to copy all these cells from thes
different sheets
into a new excel file called for example *test.xls *
Every cell value must be in a different column like A,B,C,D,E,F ....
so i get 1 line with collums of data

 

[David McRitchie]

You said that your sheets were specifically named "data1", "data2", etc.
You have to have preselected the range(s) on each of those sheets.
I expect those are not the names of the sheets. Check your last cell with Ctrl+End
does it show A1 as the last cell.

If you do not have a sheet named "data1" then you automatically
have no data. As soon as a numbered "data.." sheet is missing
that is the end of your sheets.
The cells are left to right for each row in each selection, With the
selections handled in the order they are created.

 

[captkiwi]

Hello David,

I think i do miss something which i don't understand.
I have different sheets and not all of them are called data.

it has to cycle throug the the *first sheet called data1 * has to se
the *specified cells * to copy and then *paste into a new excel * fil
called* test.xls*
then he has to *switch to a different sheet * called *data_client * an
has to copy those cells wich are specified in the script. then it has t
copy them to the * test.xls*
and so on and so on.

Problem is, lot of those cells are formula, i only need there value t
copy into the new test.xls

iam trying to understand your script but where can i specify my cel
ranges in your script.
But iam not a programmer i try to make things easier with these exce
functions.

Th

 

[David McRitchie]

In order to program something you have to know what
you want and be able to define it and code it in the
program whether it hard coded in the program or has
parameters of some kind.

First problem, how do you tell what sheets within the
workbook are to be used. The program can go through
the list of worksheets or be given a list of worksheet,
or check parts of the name of each worksheet. Or you
can group sheets and let the program pick up the names
of the selected sheets.

Second problem, are the cell ranges to be used different on
each sheet, or the same range (or ranges) to be extracted from
each sheet.

Do you want every cell in the selection, whether it is
empty, a constant or a formula, whether it is text or a number.

You have to specify limits on what you want if you want to
get every something or other; how do you know when you
are done.

Please use your name in newsgroups.

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