Programmatically changing values on grouped sheets
 

Hello all,

I have 2 sheets that are identical in format, layout, formulas. The only
difference is some cells hold values. One sheet is current period
(9/30/2007) and the other sheet is prior period (6/30/2007). We are doing
an update sooooo...the data on the Current sheet (9/30/2007) will be moved
to the Prior sheet and new data (12/31/2007) will be pasted on the Current
sheet.

If I group the 2 sheets together, then manually go into each cell on the
Current sheet, edit the cell ([F2]) and then hit enter w/o making any
changes, the data on both sheets will be the same.

But, if I group the 2 sheets together, select the data range on the Current
sheet, then run the following code, it does not make the data on the Prior
sheet match the data on the Current sheet (Prior's data is unchanged...still
6/30/2007's data and not changed to 9/30/2007's data):

Sub LoopCells()
Dim c As Range
For Each c In Selection
c.Value = "zzz" & c.Value
c.Value = Replace(c.Value, "zzz", "")
Next c
End Sub

Why not? What is a quick, easy way to accomplish this?

Thanks for any help anyone can provide,

Conan Kelly

Programmatically changing values on grouped sheets
 

try this. I selected 4 cells in col A of sheet1 and then held the ctrl key
and also selected sheet3 and then fired this using msgbox c. Seemed to work.

Sub doSelectedCellsforSelectedSheets()
'select cells in column
'ctrl and select sheets
'fire this
r1 = Selection.Cells(1, 1).Row
r2 = Rows(Selection.Row + Selection.Rows.Count - 1).Row
ac = Selection.Column
'MsgBox r1
'MsgBox r2
For Each sh In ActiveWindow.SelectedSheets
sh.Select
For Each c In ActiveSheet.Range(Cells(r1, ac), Cells(r2, ac))
'MsgBox c
c.Value = "zzz" & c.Value
c.Value = Replace(c.Value, "zzz", "")

Next c
Next sh
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Conan Kelly" wrote in message
...
Hello all,

I have 2 sheets that are identical in format, layout, formulas. The only
difference is some cells hold values. One sheet is current period
(9/30/2007) and the other sheet is prior period (6/30/2007). We are doing
an update sooooo...the data on the Current sheet (9/30/2007) will be moved
to the Prior sheet and new data (12/31/2007) will be pasted on the Current
sheet.

If I group the 2 sheets together, then manually go into each cell on the
Current sheet, edit the cell ([F2]) and then hit enter w/o making any
changes, the data on both sheets will be the same.

But, if I group the 2 sheets together, select the data range on the
Current sheet, then run the following code, it does not make the data on
the Prior sheet match the data on the Current sheet (Prior's data is
unchanged...still 6/30/2007's data and not changed to 9/30/2007's data):

Sub LoopCells()
Dim c As Range
For Each c In Selection
c.Value = "zzz" & c.Value
c.Value = Replace(c.Value, "zzz", "")
Next c
End Sub

Why not? What is a quick, easy way to accomplish this?

Thanks for any help anyone can provide,

Conan Kelly