Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell to Same Cell on Multiple Sheets
I am trying to copy a cell (A2) into the same cell across multiple sheets
(copy cell A2 in sheet1 to A2 in sheet2,sheet3, etc) Since I have bunch of sheets, what macro would I need to write in order to accomplish this. Thanks Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell to Same Cell on Multiple Sheets
Hi Mike,
Try: '========== Public Sub aTest2() Dim rng As Range Dim WB As Workbook Dim SH As Worksheet Dim WS As Worksheet Set WB = ActiveWorkbook '<<========== CHANGE Set SH = WB.Sheets("Sheet1") '<<========== CHANGE Set rng = SH.Range("A2") '<<========== CHANGE For Each WS In WB.Worksheets If WS.Name < SH.Name Then On Error Resume Next rng.Copy WS.Range(rng.Address) On Error GoTo 0 End If Next End Sub '<<========== --- Regards, Norman "Mike" wrote in message ... I am trying to copy a cell (A2) into the same cell across multiple sheets (copy cell A2 in sheet1 to A2 in sheet2,sheet3, etc) Since I have bunch of sheets, what macro would I need to write in order to accomplish this. Thanks Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell to Same Cell on Multiple Sheets
Try group the sheets together before entering the value please
"Mike" wrote: I am trying to copy a cell (A2) into the same cell across multiple sheets (copy cell A2 in sheet1 to A2 in sheet2,sheet3, etc) Since I have bunch of sheets, what macro would I need to write in order to accomplish this. Thanks Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell to Same Cell on Multiple Sheets
Norman,
That worked. Thanks. Is there a way to exclude certian sheets, lets say sheet75 and sheet76? Thanks Mike "Norman Jones" wrote in message ... Hi Mike, Try: '========== Public Sub aTest2() Dim rng As Range Dim WB As Workbook Dim SH As Worksheet Dim WS As Worksheet Set WB = ActiveWorkbook '<<========== CHANGE Set SH = WB.Sheets("Sheet1") '<<========== CHANGE Set rng = SH.Range("A2") '<<========== CHANGE For Each WS In WB.Worksheets If WS.Name < SH.Name Then On Error Resume Next rng.Copy WS.Range(rng.Address) On Error GoTo 0 End If Next End Sub '<<========== --- Regards, Norman "Mike" wrote in message ... I am trying to copy a cell (A2) into the same cell across multiple sheets (copy cell A2 in sheet1 to A2 in sheet2,sheet3, etc) Since I have bunch of sheets, what macro would I need to write in order to accomplish this. Thanks Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell to Same Cell on Multiple Sheets
Hi Mike,
And to copy the cellto specific sheets, rather than all worksheets, try: '========== Sub aTest3() Dim rng As Range Dim WB As Workbook Dim SH As Worksheet Dim WS As Worksheet Dim arr As Variant arr = Array("Sheet2", "Sheet3", "Sheet7") '<<===== CHANGE Set WB = ActiveWorkbook '<<===== CHANGE Set SH = WB.Sheets("Sheet1") '<<===== CHANGE Set rng = SH.Range("A1") '<<===== CHANGE For Each WS In WB.Worksheets If Not IsError(Application.Match(WS.Name, arr, 0)) Then On Error Resume Next rng.Copy WS.Range(rng.Address) On Error GoTo 0 End If Next End Sub '<<========== --- Regards, Norman "Norman Jones" wrote in message ... Hi Mike, Try: '========== Public Sub aTest2() Dim rng As Range Dim WB As Workbook Dim SH As Worksheet Dim WS As Worksheet Set WB = ActiveWorkbook '<<========== CHANGE Set SH = WB.Sheets("Sheet1") '<<========== CHANGE Set rng = SH.Range("A2") '<<========== CHANGE For Each WS In WB.Worksheets If WS.Name < SH.Name Then On Error Resume Next rng.Copy WS.Range(rng.Address) On Error GoTo 0 End If Next End Sub '<<========== --- Regards, Norman "Mike" wrote in message ... I am trying to copy a cell (A2) into the same cell across multiple sheets (copy cell A2 in sheet1 to A2 in sheet2,sheet3, etc) Since I have bunch of sheets, what macro would I need to write in order to accomplish this. Thanks Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell to Same Cell on Multiple Sheets
Hi Mike,
Yes, see the second version I posted. --- Regards, Norman "Mike" wrote in message ... Norman, That worked. Thanks. Is there a way to exclude certian sheets, lets say sheet75 and sheet76? Thanks Mike "Norman Jones" wrote in message ... Hi Mike, Try: '========== Public Sub aTest2() Dim rng As Range Dim WB As Workbook Dim SH As Worksheet Dim WS As Worksheet Set WB = ActiveWorkbook '<<========== CHANGE Set SH = WB.Sheets("Sheet1") '<<========== CHANGE Set rng = SH.Range("A2") '<<========== CHANGE For Each WS In WB.Worksheets If WS.Name < SH.Name Then On Error Resume Next rng.Copy WS.Range(rng.Address) On Error GoTo 0 End If Next End Sub '<<========== --- Regards, Norman "Mike" wrote in message ... I am trying to copy a cell (A2) into the same cell across multiple sheets (copy cell A2 in sheet1 to A2 in sheet2,sheet3, etc) Since I have bunch of sheets, what macro would I need to write in order to accomplish this. Thanks Mike |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell to Same Cell on Multiple Sheets
Norman,
Is there away to specify which sheets to exclude since there would too many sheets to type to include. Thanks Again - this is really helpful Mike "Norman Jones" wrote in message ... Hi Mike, And to copy the cellto specific sheets, rather than all worksheets, try: '========== Sub aTest3() Dim rng As Range Dim WB As Workbook Dim SH As Worksheet Dim WS As Worksheet Dim arr As Variant arr = Array("Sheet2", "Sheet3", "Sheet7") '<<===== CHANGE Set WB = ActiveWorkbook '<<===== CHANGE Set SH = WB.Sheets("Sheet1") '<<===== CHANGE Set rng = SH.Range("A1") '<<===== CHANGE For Each WS In WB.Worksheets If Not IsError(Application.Match(WS.Name, arr, 0)) Then On Error Resume Next rng.Copy WS.Range(rng.Address) On Error GoTo 0 End If Next End Sub '<<========== --- Regards, Norman "Norman Jones" wrote in message ... Hi Mike, Try: '========== Public Sub aTest2() Dim rng As Range Dim WB As Workbook Dim SH As Worksheet Dim WS As Worksheet Set WB = ActiveWorkbook '<<========== CHANGE Set SH = WB.Sheets("Sheet1") '<<========== CHANGE Set rng = SH.Range("A2") '<<========== CHANGE For Each WS In WB.Worksheets If WS.Name < SH.Name Then On Error Resume Next rng.Copy WS.Range(rng.Address) On Error GoTo 0 End If Next End Sub '<<========== --- Regards, Norman "Mike" wrote in message ... I am trying to copy a cell (A2) into the same cell across multiple sheets (copy cell A2 in sheet1 to A2 in sheet2,sheet3, etc) Since I have bunch of sheets, what macro would I need to write in order to accomplish this. Thanks Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
Copy cell between sheets | New Users to Excel | |||
How do I copy all cell data between sheets automaticaly? | Excel Discussion (Misc queries) | |||
same data from one cell copy to multiple sheets | Excel Worksheet Functions | |||
Copy sheets with more than 255 chars in a cell? | Excel Worksheet Functions |