| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cool Macro
1. the browser broke that line
2. yes, it can |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Uploading excel files: Useful and Cool | Excel Discussion (Misc queries) | |||
| Cool Feature - How do I do it? | Excel Discussion (Misc queries) | |||
| Can anyone tell me something really cool that Excel can do? | Excel Discussion (Misc queries) | |||
| I found this cool link | Excel Worksheet Functions | |||
| Went to your site pretty cool! | New Users to Excel | |||
Linear Mode
