Making changes on "grouped" sheets via code

[Conan Kelly]

Hello all,

If I run the following code on "grouped" sheets (more than one sheet
selected), the selected cells on ONLY the active sheet will be changed...all
other selected sheets will remain unchanged. If I were to change these
cells manually, all selected sheets will be changed.



Sub ReplaceReferences()
Dim prngCell As Range
Dim pbytNamedRange As Byte
Dim pbytIndex As Byte

pbytNamedRange = 1
pbytIndex = 1

For Each prngCell In Selection
prngCell.Formula = "'=" & Range("rngStartBals." &
pbytNamedRange).Cells(pbytIndex).Address(True, True, xlA1, True)
' ActiveWindow.SelectedSheets.prngCell.Formula = "'=" &
Range("rngStartBals." & pbytNamedRange).Cells(pbytIndex).Address(True, True,
xlA1, True)
pbytIndex = pbytIndex + 1
Next prngCell
End Sub



Is there some quick, simple way to get this to affect all selected sheets or
will I have to loop through all selected sheets and then loop through cells?
If looping through sheets then cells, no need to post modified code. I'm
pretty sure I can figure that out.

As you can tell by the commented-out line of code, I tried to qualify my
range variable with "ActiveWindow.SelectedSheets." and no workie. Hoping
for something simple like that, but will settle for looping through sheets
if necessary.

Thanks for any help anyone can provide,

Conan Kelly

 

[Jim Thomlinson]

No you will not be able to group and update via a macro. You are correct in
your assessment to loop through the sheets and update them individually...

 

[Conan Kelly]

Jim,

Thanks for the feedback.

Not quite the answer I was hoping for, but looping through sheets is not
that much work.

Thanks again for all of your help,

Conan

 

[Conan Kelly]

Jim (or others),

Kind of a follow-up question:

"Selection" is selected cell(s)/range(s) (or other selected objects...but
talking about just cells/ranges right now) on the active sheet *ONLY*,
correct?

Just out of curiosity, is it possible to access the "selected" cells on the
other sheets in the group without making those sheets active?

For example:



Sub testing()
Dim prngCell As Range
Dim pshtSheet As Worksheet

pbytNamedRange = 1

For Each pshtSheet In ActiveWindow.SelectedSheets
' pshtSheet.Activate
For Each prngCell In Selection
Debug.Print pshtSheet.prngCell.Address(False, False, xlA1, True)
Next prngCell
Next pshtSheet
End Sub


....won't work (pshtSheet.Activate is commented out) becasue prngCell is not
a member of pshtSheet. Worksheet objects don't have a "SelectedCells"
property, even though I would consider worksheets to technically have
selected cells (XL/VBA might not consider that).

Is the only way to access those cells and alter them to make their sheet
active?

Thanks again for all of your help,

Conan

 

[JLGWhiz]

You cannot do what you want with "select". However, you do not have to
activate other sheets to execute commands on them. Here are some examples:

Sheets("Sheet1") is the active sheet.
With.Sheets(2).Range("B5:H10").Font 'Changes font setting on
.Name = "Arial" 'Sheet 2 while Sheet 1
.Size = 12 'is still active and
visible.
.Bold = True
End With

This one will change text to Upper Case on sheet 3
without activating that sheet.

Dim c As Range
For Each c In Worksheets(3).Range("A1:A20:")
If Not c Is Nothing Then
If c.Value <> UCase(c.Value) Then
c.Value = UCase(c.Value)
End If
End If
Next

It just requires using routing instructions like the sheet and range
with each command, rather than activating and selecting. The
activating and selecting mimics a manual operation, whereas the
qualification method simulates the manual operation in results only.