tati
05-06-2010, 12:43 AM
Hi VB experts,
I am an SAP BI guy have a requriement to do the formating of the workbooks uisng VBA Macros.
Please fins the exact requirment.
Attached you find 2 example files with Excel makros (Makro1, started with Ctrl+Shift+x) which do the formatting of the Excel sheets (print area, page setup). What we need is a loop in the existing vba coding of the makro, which does this formatting according to the drill down in the query.
For Example PL-PERIODS:
The query itself has 12 columns with key figures. When a drilldown in columns is done, for every drill-down value 12 colums are added to the table. In the attached Excel files, the makro is formatting the first 12 columns with key figures (G20:R82). We need to enhance the makro to do this formatting for every block of 12 columns of key figures according to the drill down. In detail, the key figures start in cell G20, when there is a value in cell S20 (G20 + 12 columns), the formatting should be done for the next 12 colums (S to AD), and so on.
Additionally the makro should adjust the print area accordingly, for example for 2 pages, the print are would be F12:R82; S12:AD82
For the other example, PL-LEDGER, we need exactly the same only that the query has only 9 colums in the key figures.
I have attached the Excel workbooks for PL-Ledger and PL-Periods and adjust the makro coding here.
Please help me in providing the solution.
Regards,
santhosh.
I am an SAP BI guy have a requriement to do the formating of the workbooks uisng VBA Macros.
Please fins the exact requirment.
Attached you find 2 example files with Excel makros (Makro1, started with Ctrl+Shift+x) which do the formatting of the Excel sheets (print area, page setup). What we need is a loop in the existing vba coding of the makro, which does this formatting according to the drill down in the query.
For Example PL-PERIODS:
The query itself has 12 columns with key figures. When a drilldown in columns is done, for every drill-down value 12 colums are added to the table. In the attached Excel files, the makro is formatting the first 12 columns with key figures (G20:R82). We need to enhance the makro to do this formatting for every block of 12 columns of key figures according to the drill down. In detail, the key figures start in cell G20, when there is a value in cell S20 (G20 + 12 columns), the formatting should be done for the next 12 colums (S to AD), and so on.
Additionally the makro should adjust the print area accordingly, for example for 2 pages, the print are would be F12:R82; S12:AD82
For the other example, PL-LEDGER, we need exactly the same only that the query has only 9 colums in the key figures.
I have attached the Excel workbooks for PL-Ledger and PL-Periods and adjust the makro coding here.
Please help me in providing the solution.
Regards,
santhosh.