Vb Script to Display and reset Page No. in Excel Report

[kinrarateoh]

Have created a statement of account report in Excel that will be used to process a range of creditor codes and page breaks upon change of creditor code. This report is designed using an Excel add-in. In the layout of this report there is a Page No. field in the Header section, and for this field it is required to display 'Page No.' of 'Total Page No.'. I am trying to create a user designed output using vb that will display display the Page No and Total Page No. for each creditor statement, and this Page No. needs to be reset to start from 1 again upon each change of creditor code. How do I do that?

Regards,
kinrarateoh

[MindSpace]

You can try out this:
.Cells(iRow,1) = objUser.CN

So that it references a specific sheet, not just the Active sheet, which is does by default when you don't specify a different sheet. So, try changing it to:
.Worksheets.item(icol).Cells(iRow,1) = objUser.CN

Or perhaps that won't work, and will give an error, so instead of that, just put
.Worksheets.item(icol).Activate

right above:
.Cells(iRow,1) = objUser.CN

[Modifier]

I've got a Crystal report, which prints purchase order transaction details. It starts with the client name and and address in a page header followed by the Group Header which contains the Client trading account details , this is followed by the details section containing the transactions! Finally the page footer follows with a page count/total count.

What I need to do is to reset the page number each time Crystal hits a change of purchase order. At change of purchase order a new page will start anyway with a new header details, but the page numbering should start again with 1 and the total page count should reflect the number of pages for that purchase order alone.

You can do accordingly as per your scenerio/requirement.....might surely work for you as well

[kinrarateoh]

Dear Mindspace,

Correct me if I am wrong, but is not the vb script provided used to redirect to Another Worksheet instead of the active sheet? My report is all in one active worksheet. What I need is to tell the system to display the Page No. and Total Page No. information and to reset the page no. upon each change of creditor code.

Dear Modifier,

I understand the concept that you described, but I need to find out the Vb script to do that for me.



Any other suggestions or ideas? Thanks in advance for the kind assistance from everybody.

Regards,
kinrarateoh