[Solved] How to copy Sheet with Event macro via VBA code

[t-tymczas]

Hi.
My problem is about copying sheet with attached event macros.
I have a Calc document. One of the sheets has attached Event Macro (macro that runs every time when any cell value is changer by user). Another macro is used for copying the previous one. But the copy does not have Event macro attached anymore.
I need a VBA code that copies sheet with attached event macro, or Vba code to reassign event macto to new sheet(new copy).
Thanks for any help.

PS. Event macro is attached to specyfic sheet not globaly in whole document.

[F3K Total]

Hello,
try this:

Code: Select all

sub S_Copy_Sheet_With_Events
    oSheets = ThisComponent.sheets
    osheet = thiscomponent.currentController.activesheet
    nSheet = osheet.Rangeaddress.Sheet
    sName = osheet.Name
    sNewName = inputbox("Insert Name of copied sheet","Copy this sheet",sName)
    oSheets.copyByName( sName, sNewName, nSheet + 1)
    onewsheet = oSheets(nSheet + 1)
    aSheetEvents = osheet.Events
    sEventNames = aSheetEvents.ElementNames
    aNewSheetEvents = onewsheet.Events
    for i = 0 to ubound(aSheetEvents.ElementNames)
        aEvent = aSheetEvents.getbyname(sEventNames(i))
        aNewSheetEvents.replaceByName(sEventNames(i),aEvent)
    next i
end sub

R

[t-tymczas]

Yes. That's It.
Worked like magic )
Thanks a lot.