Developing a reporting package

[marksince1984]

Greetings all,

I am trying to build a little package which creates reports from data I
extract from Oracle. The Basic steps are:

1) Import and modify data from CSV ready for reporting - COMPLETED
2) Creation of pivot reports
3) Save/Print reports

Obviously there is a bit more detail than this.

2) Creation of pivot reports


- I need to create a new set of reports every time the code is run in
a new workbook saved as todays date.
- Pivot tables are the way to go for creating these reports
- The report content will depend on region chosen. (See screenshot)
- These regions have 5-6 unique costs centres, which need to be
seperated onto different worksheets inside the new workbook.
- Ie: If i choose region 2, then the new workbook will have
worksheets with reports for cost centres 2001,2002,2003,2004 etc, on
seperate worksheets. If i choose region 3, then the new workbook will
have worksheets with reports for cost centres 3001,3002,3003,3004 etc,
on seperate worksheets
- Each Region does not have a top-account by which we can filter a
pivot on, it will need to be filtered by the preset cost centres
(these are fixed and will never change). So in a series of accounts
from 1 to 99999, 1000-1999 is for region 1, 2001-2999 for region 2,
etc etc.

Do people understand this? I would like to get peoples understanding of
a good process which would suit.

If i break down the process into modules>

I need to create new workbook with todays date
I need to create new worksheets in that book depending on the cost
centres for the region
I then need to create pivot tables for each worksheet for JUST that
cost centre on that sheet


+-------------------------------------------------------------------+
|Filename: screen.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4856 |
+-------------------------------------------------------------------+

 

[marksince1984]

hi all,

I have written the code which will create the new worksheets in the
document depending on the option button chosen.

Can i please have suggestions how to clean this up.

Also, in the section which creates the new worksheet, i want it to skip
if the variable still equals zero (no sheet needed). What is the 'not
equal' sign to use in the IF statements?

Thanks

mark


Code:
--------------------

'SECTION ONE - ASSIGN VARIABLES

Application.EnableEvents = False

' define variables
Dim outputvar1 As Long
Dim outputvar2 As Long
Dim outputvar3 As Long
Dim outputvar4 As Long
Dim outputvar5 As Long
Dim outputvar6 As Long

'assign default values to variables
outputvar1 = 0
outputvar2 = 0
outputvar3 = 0
outputvar4 = 0
outputvar5 = 0
outputvar6 = 0


'assign values to variables depending on radio button selected

If OptionButton1.Value = True Then
outputvar1 = 100100
outputvar2 = 100200
outputvar3 = 100300
outputvar4 = 100400
outputvar5 = 100500
outputvar6 = 100600
End If

If OptionButton2.Value = True Then
outputvar1 = 100241
outputvar2 = 100242
outputvar3 = 100243
outputvar4 = 100244
outputvar5 = 100245
outputvar6 = 0
End If

If OptionButton3.Value = True Then
outputvar1 = 300100
outputvar2 = 300200
outputvar3 = 300300
outputvar4 = 300400
outputvar5 = 300500
outputvar6 = 300600
End If

If OptionButton4.Value = True Then
outputvar1 = 400100
outputvar2 = 400200
outputvar3 = 400300
outputvar4 = 400400
outputvar5 = 400500
outputvar6 = 0
End If



' create new worksheets for each individual cost centre based on variables

Set NewSheet = Worksheets.Add
NewSheet.Name = outputvar1

Set NewSheet = Worksheets.Add
NewSheet.Name = outputvar2

Set NewSheet = Worksheets.Add
NewSheet.Name = outputvar3

Set NewSheet = Worksheets.Add
NewSheet.Name = outputvar4

Set NewSheet = Worksheets.Add
NewSheet.Name = outputvar5

Set NewSheet = Worksheets.Add
NewSheet.Name = outputvar6

'Return to main worksheet

Worksheets("Main").Activate

'Return Messages

MsgBox "Process complete"





End Sub

 

[marksince1984]

When i run this code, it gives me error in not recognising the variable
"outputvar1' which has already been declared earlier in the piece??

Any ideas??



Code:
--------------------

'SECTION THREE - create pivot tables on each worksheet with level one filters set to variable

Dim WS As Worksheet
Dim PC As PivotCache
Dim PT As PivotTable
Dim PivotRange As Range
Dim FinalRow As Long



'Create pivot on outputvar1

Set WS = Worksheets("Main")

'Define input area and setup a pivot cache
FinalRow = WS.Cells(65536, 1).End(xlUp).Row
Set PivotRange = WS.Cells(1, 1).Resize(FinalRow, 8)
Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PivotRange.Address)

'create table
Set PT = PC.CreatePivotTable(TableDestination:=outputvar1.Range("A10"), TableName:=outputvar1)

 

[Guest]

Hi you can write e.g.

IF x <> 0 Then