Cool Macro

  • Thread starter Thread starter carla 7
  • Start date Start date
C

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.
 
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
 
The macro did create a new workbook but did not copy the values from the
worksheets over. Thanks anyway.
 
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
 
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....
 
You would have to have some ay of knowing which workbook that was, and where
it was.
 
Back
Top