Nov 05 2022 09:35 PM
I am lost here, trying to update the same Table Range across 600 plus sheets at once.
Any suggestions and or ideas .
Thank you
Bill
Nov 06 2022 01:21 AM
@BillHull Well, your intended "update" is vague:
But in general, you need a VBA procedure (macro) that goes through all the sheets and (conditionally) makes the updates. Consider building code from these code scraps:
Sub SpecialBHUpdate()
Dim objWorksheet As Worksheet
Dim strSheetName As String
Dim strNewValue As String
Dim vntNewValue As Variant
Dim blnContentIsFormula As Boolean
'----
For Each objWorksheet In ActiveWorkbook.Sheets
strSheetName = objWorksheet.Name
' -- Exclude some worksheets based on their name.
If StrComp(strSheetName, "<excluded_sheet_1>", vbTextCompare) = 0 Then
GoTo NextWorksheet
End If
'...repeat that block as needed.
' --
objWorksheet.Activate
' -- For a constant value...
vntNewValue = 2023 'e.g., or...
vntNewValue = "<2023-related text value>"
' -- If you need to see the sheet to decide on a value...
strNewValue = InputBox("What should the content be for this sheet?")
vntNewValue = strNewValue '...possibly including some data-type coversion
'...and maybe...
If Left$(strNewValue, 1) = "=" Then
blnContentIsFormula = True
Else
blnContentIsFormula = False
End If
'...etc.
' -- IF THE UPDATES ARE TO AN EXCEL TABLE:
'...WRITE CODE TO DETERMINE THE TABLE NAME; THEN
With objWorksheet.ListObjects(<the_table_name_for_this_sheet>)
' NOTE: IT'S YOUR RESPONSIBILITY TO DETERMINE AND SPECIFY THE
' CORRECT ADDRESSES:
If blnContentIsFormula = True Then
.DataBodyRange(1, 3).Formula = strNewValue
Else
.DataBodyRange(1, 3).Value = vntNewValue
End If
End With
' -- OTOH, IF THE UPDATES ARE TO AN EXCEL RANGE:
With objWorksheet.Range("B17")
' NOTE: IT'S YOUR RESPONSIBILITY TO DETERMINE AND SPECIFY THE
' CORRECT ADDRESS ABOVE.
If blnContentIsFormula = True Then
.Formula = strNewValue
Else
.Value = vntNewValue
End If
End With
NextWorksheet:
Next objWorksheet
End Sub
Most important: Test your code on a COPY of the important workbook, repeatedly, until you are convinced the code works correctly.
Nov 06 2022 02:03 AM
Nov 06 2022 02:07 AM