For macros is it possible to target multiple sheets at once?

[michaely]

Im currently using this code to delete the cell contents in sheet no. one. But im wondering what changes I should make so it will delete the contents in mutlipel sheets? Eg sheet one-seven

Sub DeleteRangeTest

Dim cell_range as Object

document = ThisComponent

sheet = document.sheets(0)

cell_range =
sheet.getCellRangeByName(“B2:N999”)

cell_range.clearContents(1023)

End Sub

[Ratslinger]

Hello,

You can just loop through the sheets:

Option Explicit

Sub DeleteRangeTest
    Dim cell_range as Object
    Dim sheet as Object
    Dim iCount as Integer
    For iCount = 0 to 6
        sheet = ThisComponent.sheets(iCount)
        cell_range = sheet.getCellRangeByName("B2:N999")
        cell_range.clearContents(1023)
    Next
End Sub

[Lupp]

Of course it’s reasonable to do it as @Ratslinger suggested.

Just for completeness: You can also

first collect thge ranges from one or more sheets, and
then apply the clearContents method to the collection.

Sub demo()
doc = ThisComponent
rgs = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
Dim aRgA As New com.sun.star.table.CellRangeAddress
With aRgA
  .StartColumn = 1
  .EndColumn = 13
  .StartRow = 1
  .EndRow = 998
  For j = 0 To 6
    .sheet = j
    rgs.addRangeAddress(aRgA, False)
  Next j
End With
rgs.clearContents(1023) REM To clear all content
End Sub

[michaely]

sorry for replying to an old post but I’ve another question

can you tell me what changes I should make to this macro so that it’l target multiple sheets

Im currently using this code to add cell content in the active sheet, it enters “rob” in the in the cell directly left of a call that’s contains text “rory”. But im wondering what changes I should make so it will do this in mutliple sheets?

Sub SearchAndDoStuff3
Const needle = "Rory "
Const searchRange = "A1:Z1500"
Set oSheet = ThisComponent.CurrentController.ActiveSheet
Set oRange = oSheet.getCellRangeByName(searchRange)
 For i = 0 To oRange.Rows.getCount() - 1
 For j = 0 To oRange.Columns.getCount() - 1 
        Set oCell = oRange.getCellByPosition( j, i )
        idx = InStr(oCell.String,needle)
        If (  idx > 0) Then
                oCell = oRange.getCellByPosition( j-1, i )
                oCell.String = "Rob"
        Endif
    Next
Next 

End Sub