Primavera WBS Bands in Excel

Hi Friends,

 

This is my First Blog , welcome you all

 

Now we all planners who uses Primavera are  wasting our time in transferring the program from primavera to excel , because of WBS banding is not getting copied with colors, so we have to create it by conditional formatting or some other methods but those are time consuming , Today as you saw in my video just with a single click you can make it possible with excel VBA codes which we are going to learn

 

Lets Start

 

First create a new excel file which should be macro enabled and named as "Primavera WBS bands"

 

 

 

Rename the sheet tab as

 

 

Copy any program for which you want to create the WBS  bands in excel

 

From cell E4

 And now we need to create a formula which need to give as the level of WBS in the B columns

 

In Cell B5 write the formula as shown below

 

= IF(AND(E5="",F5=""),"",IF(F5="",FIND(LEFT(TRIM(E5),1),E5),0))

 

And drag till your program end activity.

 

  

now press Alt+F11

 which will open the VBA Coding Window,  go to insert tab and insert a module and rename as WBSbands

 

 

Now we will start writing the code we will start with

 

Sub WBSbands ()

Code will come here this is the syntax of VBA

End sub

The Logic here is we will take the reference of Column B which is WBS levels and based on there values we will Fill the interior color for our program WBS

Lets begin

First of all we need to define our variables we are using following variables

i as integer ( for For loop)

rg as range (for selecting the working cells)

c as integer (for counting no. of cells )

testcell as Range (test cell)

target as Range

we should write “Dim” before defining any variable

 

 

Now we have to set our working area  write the code

Set rg = Application.Workbooks("Primavera WBS Bands.xlsm").Worksheets("Bands").Range("B5", Range("B5").End(xlDown))

Here I gave the reference of" B5" to "Xldown" which is our WBS level column

Assign c for counting the cells

C = rg.Cells.Count

Now to check our code write a  test massage

MsgBox "TOTAL CELLS = " & C

That should show you a pop massage showing the count

delet the MsgBox "TOTAL CELLS = " & C code after checking

Write a for loop

For i = 0 To C

Make a test cell for our further works

Set testcell = rg(i)

                               

Now we will select the area where our program is copied  that is E to L and varied cell number

Set target = Range(testcell.Offset(0, 3), testcell.Offset(0, 10))

 

We will use “case”  function for our further codes

Select Case rg(i).Value

This case value is the WBS level

Our project name is WBS level 1

So we will write as           Case Is = 1

write the following code for applying interior color ,font size ,row heights etc.

 

                Case Is = 1

 

        target.Select

        With Selection

        

         .Interior.Color = RGB(0, 112, 192)

         .Font.Size = 20

         .Font.Color = RGB(255, 255, 0)

         .Font.Bold = True

         .RowHeight = 24

   

        End With

 

Like this we can write further for 3,5 7 and so on , which is odd numbers

You can define the interior color by RGB

 

At the don’t forget to write the following code

 

End Select

Next

Press F8 to execute the code line by line or press F5

 

The final result will be

 

we have to create a botton in our sheet for that go to developers tab click the Design Mode and add botton

  Rename the Botton as WBS Band and switch off the design mode

now click the botton and see the magic

we have to create another botton for reset with this code

Sub refreshwbs()

Application.Workbooks("Primavera WBS Bands.xlsm").Worksheets("Bands").Range("E5:L5", Range("E5:L5").End(xlDown)).Select 'SELECT AND CLEAR THE ENTIRE DATA TABLE
Selection.ClearContents

Application.ScreenUpdating = False

With Selection 'RESET CELL AND FONT TO DEFAULT SETTING
    .Interior.ColorIndex = x1None
    .Font.Size = 11
    .Font.Bold = False
    .Font.Color = RGB(0, 0, 0)
    .RowHeight = 15
    .Font.Italic = False
End With

With Selection.Borders(xlEdgeLeft) 'REMOVE THE BORDERS
    .LineStyle = xlNone
End With

With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlNone
End With

With Selection.Borders(xlEdgeRight)
    .LineStyle = xlNone
End With

With Selection.Borders(xlInsideVertical)
    .LineStyle = xlNone
End With

With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlNone
End With

Application.ScreenUpdating = True

End Sub

Enjoy

Thanks for your interest If you have any doubt put into comments