copy certain cell values from different sheets to a new excel file

[captkiwi]

Hello everybody,

i have a excel workbook with different sheets.

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

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


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

from 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 just
need de VALUE from this formula.

after the selection i would like to copy all these cells from these
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?

is there away to solve this problem ?

 

[Tom Ogilvy]

Dim rng as Range
set rng = worksheets("Dest").Cells(rows.count,1).end(xlup)(2)
worksheets("Sheet1").Range("D24").Copy Destination:=rng
Worksheets("Sheet1").Range("F23").Copy Destination:=rng.offset(0,1)

and so forth.