Calculate data from different sheets

[Stefan]

Hi,

I´m looking for a formula that will transfer certain data to different sheets.

For example:
Data in sheet 1
If cell a1=usk data in cell a2,a3...
Transfer data to sheet 2
If cell a1=ssk data in cell a2, a3...
Transfer data to sheet 3

The easiest way would be to make another column but this will make the sheet
to big to handle.

Regards
Stefan

 

[joel]

You need a macro. Where does the data go in sheets 2 and 3?

 

[Stefan]

The data in sheet 2 and 3 is an average of the data printed in sheet 1.
For example:
The average of a2-a5 in sheet 1 goes to a2 in sheet 2 if a1 in sheet 1 equal
ssk

 

[joel]

I don't think your request is correct. I asume in sheet 1 the Type is in
column A and the values you want averaged is in column B to E. The averages
wil go in column A in the sheet specified.


Sub MoveData()

With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""

Select Case DataType

Case "USK"
Set Sht = Sheets("Sheet2")

Case "SSK"
Set Sht = Sheets("Sheet3")
End Select

LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
Newrow = LastRow + 1

Set DataRange = .Range("B" & RowCount & ":E" & RowCount)
Average = WorksheetFunction.Average(DataRange)

Sht.Range("A" & Newrow) = Average

RowCount = RowCount + 1
Loop
End With


End Sub

 

[Stefan]

Thanks Joel, this will do it

I don't think your request is correct. I asume in sheet 1 the Type is in
column A and the values you want averaged is in column B to E. The averages
wil go in column A in the sheet specified.


Sub MoveData()

With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""

Select Case DataType

Case "USK"
Set Sht = Sheets("Sheet2")

Case "SSK"
Set Sht = Sheets("Sheet3")
End Select

LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
Newrow = LastRow + 1

Set DataRange = .Range("B" & RowCount & ":E" & RowCount)
Average = WorksheetFunction.Average(DataRange)

Sht.Range("A" & Newrow) = Average

RowCount = RowCount + 1
Loop
End With


End Sub