Macro to copy cells & paste into active cell

[DeraJJ]

Hi
Just looking for a way to copy the cells (A1:D1) then paste them into my active cell
The active cell changes all the time so I need the macro to paste A1:D1 my active cell.
I will be copying and pasting other info so I can’t just keep A1:D1 on the clipboard, I need to reselect it, copy it, then paste it onto my active cell.

I had a way of doing this in Google Sheets, but I can’t find a way to create a macro to do this for me here.
I can copy the cells with a macro, I can paste with another macro, but I can’t put the two together.
Sorry I can’t find the answer anywhere. Please let me know if you require further info.
Thanks so much

[Zizi64]

Do you want to copy the formatting properties too, or only the cell content (strings? values? formulas?)?

What operating system are you using?
The Windows 10 has a new Clipboard manager function. Try it: hit WinKey-V

[DeraJJ]

Hi. Just the contents of the cell. Thanks, sorry I didn’t mention that

[DeraJJ]

Just a note to add, I want to paste the contents into a range of cells, starting with my active cell. So if I am copying A1:D1, I want to paste it into G7:J7 (where G7 is my active cell). Hope that makes sense

[DeraJJ]

Yes windows 10. I’ll try to have a look at the WinKey-V function

[DeraJJ]

Not happening with the Windows Key Paste function. When I copy something else and try to paste A1:D1 again, it says “The contents of the clipboard could not be pasted”.

[Zizi64]

You must switch On this function of the Win10 before.
But I just heard this new function now in an another thread of the AskLibo, but I never tried it before.
I just tried it now, and it works for me in my Win10x64Prof and with LO 6.2.8
It must to pop-up a little panel at the right-bottom of the screen with message “switch on the feature” or something similar else. And there is a button on the panel to switch on the function. If this panel not popping up for you (when you hit WinKey-V), then your Win10 has not updated recently.

[DeraJJ]

Re: Win Key & Paste. Yes I have got it turned on. I got the pop up window and selected what to paste and then it comes up with the error that I mentioned before. Not sure if that has to do with any formatting etc on my worksheet. Thanks for the info on that, I wasn’t aware of it. It will be a good tool to have

[JohnSUN]

It could be like this:

Sub copyHere
Const SOURCE_RANGE = "A1:D1"
Dim oCurrentController As Variant,  oSheet As Variant
Dim oCellRangeByName As Variant,  aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim oCurrentSelection As Variant
Dim oCellByPosition As Variant,  aCellAddress As New com.sun.star.table.CellAddress

	oCurrentController = ThisComponent.getCurrentController()
	oSheet = oCurrentController.getActiveSheet()
	
	oCellRangeByName = oSheet.getCellRangeByName(SOURCE_RANGE)
	aRangeAddress = oCellRangeByName.getRangeAddress()
	
	oCurrentSelection = ThisComponent.getCurrentSelection()
	If oCurrentSelection.supportsService("com.sun.star.sheet.SheetCellRanges") Then oCurrentSelection = oCurrentSelection.getByIndex(0)
	oCellByPosition = oCurrentSelection.getCellByPosition(0, 0)
	aCellAddress = oCellByPosition.getCellAddress()
	
	oSheet.copyRange(aCellAddress, aRangeAddress)
End Sub

There is one inaccuracy in this code - the contents of the specified cells will not be inserted into the Active cell, but into the upper left selected cell

[DeraJJ]

BINGO!!! Thanks so much, it’s doing exactly what I wanted. Really appreciate the help and solution.