Macro to Copy Range of data to new spreadsheet

[Kenneth08]

Hello Friends,

Newbie here in Basic programming.
Can you help me for the code on copying range to new spreadsheet without closing the original file.
The copied file should close and the original file will active.

Hoping you can help.

Some details:
Spreadsheet1 Name = Macro101
Sheet Name = Macro
Range = A3:I100

Spreadsheet2 Name = Savefile_08252017 (08252017 is date today)
Range = A3:I100

[JohnSUN-Pensioner]

Target cell (start of range) is A1? Or A3?

[Kenneth08]

Target cell (start of range) is A1? Or A3?

I revisedr

Some details:
Spreadsheet1 Name = Macro101
Sheet Name = Macro
Range = A3:I100

Spreadsheet2 Name = Savefile_08252017 (08252017 is date today)
Range = A3:I100

[Zizi64]

Use the API functions for crating a new empty Calc document (or for opening an existing one)
viewtopic.php?f=20&t=44142&hilit=loadcomponentfromURL

What you want to copy: texts, numbers, formulas, direct formatting properties, cell styles???
There are many ways to achieve this task by a macro code...


Finally you can save (store) the new document, and you can close it by API functions:
StoreToURL() or StoreAsURL(), oDoc.Close() or oDoc.Dispose()


Please, please, please READ the descriptions that we suggested for you in the past: Andrew Pitonyak's macro books, and the API descriptions...

[JohnSUN-Pensioner]

It can be something like as

Code: Select all

Sub StoreRange
Const SHEET_TO_COPY = "Macro"
Const PREFIX_FILENAME = "Savefile_"
Dim cDoc As Variant 		' Current document
Dim oDoc As Variant 		' Output document
Dim oSheets As Variant 		' All sheets of output document
Dim oSheet As Variant 		' Single sheet of output document
Dim i As Long
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	cDoc = ThisComponent
	cDoc.store	' Save document before linking!

REM Output file

	oDoc = CreateNewDocument("scalc")
	oSheets = oDoc.getSheets()
	For i = oSheets.getCount()-1 To 1 Step -1
		oSheet = oSheets.getByIndex(i)
		oSheets.removeByName(oSheet.getName())
	Next i
	oSheet = oSheets.getByIndex(0)
	oSheet.link(cDoc.getURL(), SHEET_TO_COPY, "calc8", "", com.sun.star.sheet.SheetLinkMode.VALUE)
	oSheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
Rem Delete unnecessary data (if they exist) 
Rem If you want data "as is" then just remove this lines
Rem Row of A3 - 2, previous row 1 (last param of getCellRangeByPosition)
	oSheet.getCellRangeByPosition(0, 0, oSheet.getColumns().getCount-1, 1).clearContents(1023)
Rem From last row (99) to end of sheet
	oSheet.getCellRangeByPosition(0, 100, oSheet.getColumns().getCount-1, oSheet.getRows().getCount-1).clearContents(1023)
Rem and right part of sheet
	oSheet.getCellRangeByPosition(9, 0, oSheet.getColumns().getCount-1, oSheet.getRows().getCount-1).clearContents(1023)
Rem Save result
	oDoc.StoreAsURL(DirectoryNameoutofPath(cDoc.getURL(), "/") + "/" + PREFIX_FILENAME + _
		Format(Now,"DDMMYYYY") + ".ods", Array())
	oDoc.close(True)
End Sub