Thanks for your reply, I am getting a compile error:syntax error message for
line:
Paste:=xlValues. But the macro seems to be the right one except for the
syntax error.
"Jarek Kujawa" wrote:

try this macro

Sub kopiuj()

For Each Worksheet In ActiveWorkbook.Worksheets
i = i + 1
If i < ActiveWorkbook.Worksheets.Count Then
Worksheets(i).Range("M6").Copy
Worksheets(ActiveWorkbook.Worksheets.Count).Cells( i, 1).PasteSpecial
Paste:=xlValues
End If
Next

End Sub

pastes values from M6 in any worksheet (but the last one) to the last
one, starting from A1

adjust yr ranges as necessary