[Solved] Macro help

[j68r]

Apologies, I am very green when it comes to all things macro.

That said, I would like an elegant solution if possible that allows a macro to be called from the active sheet, that performs simple operation(s) on another specified cell in another specified sheet without changing focus from the current cell/sheet.

For example

call this macro from somewhere within sheet_1 (or any other cell/sheet)

that then cuts and pastes the content of cell_A1 in sheet 2

but in doing so doesn't move the focus from the active cell in sheet_1 (or any other cell/sheet) prior to the macro call being made.

TIA

[FJCC]

Do I understand correctly that you want to copy the content of Sheet2.A1 into whatever cell is currently active and clear Sheet2.A1?

[j68r]

No, I should have made that much clearer.

Let's say I'm in Sheet1.A1, I then call a macro via a button that cuts the contents in Sheet2.A1 and pastes the contents back in sheet2.A1

I want to remain in Sheet1.A1, a cut paste macro would leave me in sheet2.A1

I can write a macro that points me back to a specific cell in a specific sheet after the cut paste function completes but what I want is the focus to remain on whatever sheet/cell I was in prior to calling the macro, without having to write several macros with specific redirections to be used in several sheets.

Hope that's a little clearer. TIA

[FJCC]

Let's say I'm in Sheet1.A1, I then call a macro via a button that cuts the contents in Sheet2.A1 and pastes the contents back in sheet2.A1

That says you cut from Sheet2.A1 paste back in the same cell. I assume there is a typo somewhere.

When you say "cut", I take that to mean that the source cell is left blank. Is that what you mean?

[j68r]

Yes, removing the contents of the sheet2.A1 cell to cache and then restoring back in to the sheet2.A1 cell.

That part of the macro isn't the issue, it's stopping that process (or any other) when called, making sheet2.A1 the new focus (active cell).

[FJCC]

Since cutting from and pasting into the same cell changes nothing, I assume there is some side effect of this process that you are interested in. Can you explain what the ultimate goal is? There may be a simpler way to do it.

[Zizi64]

Hope that's a little clearer.

Not, really.

You can jump to an another sheet, and then you can jump back to the original sheet by a recorded or a written macro.

And you can get/set the cell values in the whole spreadsheet document without jumping from/to any cells of any sheets by calling some API function from your macro.

What do you want to achieve really?

[UnklDonald418]

If I understand correctly try something like

Code: Select all

'save original cell
oOriginalCell = ThisComponent.getCurrentSelection()
'Pasting to another location will change focus, nothing can be done about that
'but after doing the other operations
' you can return to the original cell
ThisComponent.CurrentController.Select(oOriginalCell )

[j68r]

Thank you, that works a treat UnkDonald418

I had to remove the double dot in .getcurrentselection()

I appreciate it's a strange request but the result of adding these lines to the existing code is that it now behaves exactly as required.

thanks again.

[Lupp]

Thank you, that works a treat UnkDonald418 ...
... the result of adding these lines to the existing code is that it now behaves exactly as required.
...

How would the existing code behave if the Sub is called while not only a single CellRange is selected?
The "added lines" seem to take the single-cell-case for assured, but will also work if the selection is a SheetCellRange or even if a SheetCellRanges selection was made.

Pasting to another location will change focus, nothing can be done about that

This is true if the pasting is done by a DispatchHelper command (".uno:Paste") which requires anyway to define the target by selecting a cell in advance ("uno.GotoCell").
Copying contents elsewhere by

Code: Select all

anySheet.copyRange(targetCell.CellAddress, sourceRange.RangeAddress)

doesn't afflict the selection.