changes toTables,

Occasional Contributor

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

 

 

3 Replies

@BillHull Well, your intended "update" is vague:

  1. You wrote "Table Range".  Are the relevant cells in Excel tables, or in Excel ranges of cells?
  2. If in Excel tables, are you updating cells in the header row range, the data row range, or the totals row range (or a combination)?  Do your tables all have the same name (with worksheet scope) or individual names (probably with workbook scope)?
  3. Will the new content be a literal value (or values)?  The same literal value(s) for all sheets?  Different values that you (the user) have to determine while looking at each sheet?  Should it be a formula  (or formulas)?  Something else?
  4. Does the workbook contain sheets that should not be updated?

 

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. 

 

Thanks Snowman55, i am working in Tables,
all my sheets are identical , with the header row and the next row below containing the results of each columns .
I used the Table for each sheet to make my Query from for Multiple Sheets however i now needed to add more columns, which fell outside of the original Table for each sheet .
Thanks for the help. I will have to make sense of it then try it out
cheers and thanks
Bill
My plan was to expand each table for sheet to include the new columns , i can do this one sheet at a time at the moment , will take forever at this point . Looking though what you have suggested.. thanks heaps
cheers