|
Cool Macro
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. |
Cool Macro
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 -- __________________________________ HTH Bob "carla 7" wrote in message ... 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. |
Cool Macro
1. the browser broke that line
2. yes, it can |
Cool Macro
The macro did create a new workbook but did not copy the values from the
worksheets over. Thanks anyway. "Bob Phillips" wrote: 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 -- __________________________________ HTH Bob "carla 7" wrote in message ... 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. |
Cool Macro
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 -- __________________________________ HTH Bob "Bob Phillips" wrote in message ... 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 -- __________________________________ HTH Bob "carla 7" wrote in message ... 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. |
Cool Macro
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" wrote: 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 -- __________________________________ HTH Bob "Bob Phillips" wrote in message ... 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 -- __________________________________ HTH Bob "carla 7" wrote in message ... 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. |
Cool Macro
You would have to have some ay of knowing which workbook that was, and where
it was. -- __________________________________ HTH Bob "carla 7" wrote in message ... 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" wrote: 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 -- __________________________________ HTH Bob "Bob Phillips" wrote in message ... 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 -- __________________________________ HTH Bob "carla 7" wrote in message ... 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. |
| All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com