Cool Macro

[carla 7]

Thanks Jarek. This last 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

is a saver. Notice I removed the colon for it to work. Can this macro be
tweaked further to paste values to a new workbook instead of a worksheet.
Just asking.

 

[Bob Phillips]

Dim sh As Worksheet
Dim wb As Workbook

Set wb = Workbooks.Add
For Each sh In ActiveWorkbook.Worksheets

i = i + 1
If i < ActiveWorkbook.Worksheets.Count Then

Worksheets(i).Range("M6").Copy
wb.Worksheets(1).Cells(i, 1).PasteSpecial Paste:=xlValues
End If
Next

 

[Jarek Kujawa]

1. the browser broke that line

2. yes, it can

 

[carla 7]

The macro did create a new workbook but did not copy the values from the
worksheets over. Thanks anyway.

 

[Bob Phillips]

Perhaps it needs this tweak

Dim sh As Worksheet
Dim wb As Workbook
Dim this As Workbook

Set this = ActiveWorkbook
Set wb = Workbooks.Add
For Each sh In this.Worksheets

i = i + 1
If i < this.Worksheets.Count Then

this.Worksheets(i).Range("M6").Copy
wb.Worksheets(1).Cells(i, 1).PasteSpecial Paste:=xlValues
End If
Next

 

[carla 7]

Another lifesaving macro...question though, instead of the values being
placed in different workbooks after each time the macro is run, can they be
placed in the destination workbook where the values were first pasted?

Excitedly curious....

 

[Bob Phillips]

You would have to have some ay of knowing which workbook that was, and where
it was.