Need help with macro that prints pages based on said rules

[b1sothefirst]

Hi,
I have a Calc Project with different sheets. In Sheet1 information is writen and in Sheet2, this information is distributed to different cells, ready to print.

I want to create a macro that looks for the value of cell B15 /sheet2/ and prints that many copies of page 1 (page 1 is cells A1:F49 /sheet 2/ ). Then I want it to look for the value of cell B64 /sheet2/ and print that many copies of page 2 (page 2 is cells A50:F98 /sheet2/ ), ect./there are 35 pages total/

I created a macro for test purposes, with one sheet only. The problem with it is that it prints ONLY the second range of cells (page 2) and it prints it B15 + B64 times. For example B15 = 2, B64 = 1. Page 1 /A1:F49/ doesn’t print at all, Page 2 /A50:F98/ is printed 3 times.

Can you give me any suggestions on what to change and how to make it happen /if it’s even possible/

This is my code:

Sub PrintCopies()

Dim iCopiesPage1 As Integer
Dim jCopiesPage2 As Integer
Dim oSheet As Object
Set oSheet = ThisComponent.Sheets(0)
 iCopiesPage1 = oSheet.getCellRangeByName("B15").getValue()
If IsNumeric(iCopiesPage1) And iCopiesPage1 > 0 Then
    oSheet.PrintAreas = Array(oSheet.getCellRangeByName("A1:F49").RangeAddress)
    For i = 1 To iCopiesPage1
        ThisComponent.print(Array())
    Next i
Else
    MsgBox "Please enter a numeric value greater than 0 in cell B15"
End If

jCopiesPage2 = oSheet.getCellRangeByName("B64").getValue()
If IsNumeric(jCopiesPage2) And jCopiesPage2 > 0 Then
    oSheet.PrintAreas = Array(oSheet.getCellRangeByName("A50:F98").RangeAddress)
    For j = 1 To jCopiesPage2
        ThisComponent.print(Array())
    Next j
Else
    MsgBox "Please enter a numeric value greater than 0 in cell B64"
End If
End Sub

Thank you in advance!

[KamilLanda]

Test this, it adds the same range to the output array more times, accorging to Value from Cell

Sub printRanges
	dim ranges()
	ranges=array( array("B15","A1:F49"), array("B64", "A50:F98") ) 'array with count-cells and ranges
	const max=1000 'it supposes maximum pages to print
	dim p(max) 'array with ranges to print
	
	dim oDoc as object, oSheet as object, sCell$, i%, j%, iCopies%, iData%, args1(0) as new com.sun.star.beans.PropertyValue
	oDoc=ThisComponent
	oSheet=oDoc.Sheets(0) 'sheet for print
	
	for i=lbound(ranges) to ubound(ranges)
		sCell=ranges(i)(0) 'cell for Count
		iCopies=CInt(oSheet.getCellRangeByName(sCell).getValue()) 'Count
		if iCopies>0 then
			for j=0 to iCopies-1 'add the same range according to Count
				p(iData)=oSheet.getCellRangeByName(ranges(i)(1)).RangeAddress 'add range for print
				iData=iData+1
			next j
		else
			msgbox("Please enter a numeric value>0 in cell: " & chr(13) & sCell, 16)
			oDoc.CurrentController.Select(oSheet.getCellRangeByName(sCell)) 'select bad cell
			stop
		end if
	next i
	
	redim preserve p(iData-1) 'only real Count of pages
	oSheet.PrintAreas=p
	oDoc.print(Array())
End Sub

[b1sothefirst]

It works for the Test project, thanks!

If I want to add more print areas, should I just add them to ranges=array? For example if i change it to:

   ranges=array( array("B15","A1:F49"), array("B64", "A50:F98"), array("B113", "A99:F147"), array("B162", "A148:F196"))

will it still work?

Also, can I make it if the cell that contains the number of copies that need to be printed, is = 0 to print everything before it. For example with the code above if B113=0, not to give me an error mesege, but to print A1:F49, B15 times and A50:F98, B64 times and to end the macro?

[KamilLanda]

Yes, put next ranges to variable ranges and run it :-). You can test it on some virtual PDF printer, not to print on papers :-).
There is the condition if iCopies>0 then, so if B113 is zero, then it will not add the range to output array p().


If your ranges are always increased by +49, then it is possible to modify macro to take ranges automatically and not from variable ranges.