Sophisticated VB coding for record macro

S

sshrikanth2

Board Regular
Joined
Jan 17, 2012
Messages
138
Dear All,

Below is the coding i had recorded for preparing a dashboard. But i want these macro codings to be more sophisticated and reduce the file size and the current file size is 6.98 MB. When i run macro, the formulas appear in the cells and i believe that's the reason why the file size increased as most of the formulas are based on the volatile function. Kindly help me out to input the sophisticated VB coding and to reduce the file size.


Private Sub CommandButton1_Click()

Sheets("P2P Dashboard").Unprotect Password:="123"
Range("C8:H12").Select
Range("C8:H12").ClearContents
Range("J8:M12").Select
Range("J8:M12").ClearContents
Range("O8:P12").Select
Range("O8:P12").ClearContents
Range("T8:Y12").Select
Range("T8:Y12").ClearContents
Range("C17:H38").Select
Range("C17:H38").ClearContents
Range("J17:L38").Select
Range("J17:L38").ClearContents
Range("C43:I64").Select
Range("C43:I64").ClearContents
Range("K43:K64").Select
Range("K43:K64").ClearContents
' ---------------------------------
Range("C8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-1],Consolidation!R4C67:R15000C67,""<>"")"
Range("C8").Select
Selection.Copy
Range("C8:C12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' ----------------------------------------------------------------------------------------
' "Sum of line items"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C32:R15000C32,Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-2])"
Range("D8").Select
Selection.Copy
Range("D8:D12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' ----------------------------------------------------------------------------------------
' "Keyed"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-3],Consolidation!R4C7:R15000C7,'P2P Dashboard'!R7C5)"
Range("E8").Select
Selection.Copy
Range("E8:E12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' --------------------------------------------------------------------------------
' "Referred"
Range("F8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-4],Consolidation!R4C7:R15000C7,'P2P Dashboard'!R7C6)"
Range("F8").Select
Selection.Copy
Range("F8:F12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' ---------------------------------------------------------------------------------
' "Total Time Taken"
Range("G8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C38:R15000C38,Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-5])"
Range("G8").Select
Selection.Copy
Range("G8:G12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' ---------------------------------------------------------------------------------
' "Actual Productivity"
Range("H8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=0,""0.00"",RC[-5]/RC[-1]/R1C1)"
Range("H8").Select
Selection.Copy
Range("H8:H12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'------------------------------------------------------------------------------
'"Final Productivity"
Range("J8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,""0.00"",RC[-2]/RC[-1]*R2C1)"
Range("A2").Select
ActiveCell.FormulaR1C1 = "100"
Range("J8").Select
Selection.Copy
Range("J8:J12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'------------------------------------------------------------------------------
' "Need to rekey"
Range("K8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-9],Consolidation!R4C7:R15000C7,'P2P Dashboard'!R7C11)"
Range("K8").Select
Selection.Copy
Range("K8:K12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'-------------------------------------------------------------------------------
' "Time taken for QC"

Range("L8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C28:R15000C28,Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-10])"
Range("L8").Select
Selection.Copy
Range("L8:L12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' ---------------------------------------------------------------------------
' "Actual Productivity"
Range("M8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-10]=0,""0.00"",RC[-10]/RC[-1]/R1C1)"
Range("M8").Select
Selection.Copy
Range("M8:M12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' ----------------------------------------------------------------------------
' "Final Prodcutivity"
Range("O8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,""0.00"",RC[-2]/RC[-1]*R[-6]C[-14])"
Range("O8").Select
Selection.Copy
Range("O8:O12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' -----------------------------------------------------------------------------
' "FPY"
Range("P8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-13]=0,""0.00"",(RC[-13]-RC[-5])/RC[-13])*100"
Range("P8").Select
Selection.Copy
Range("P8:P12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("P8").Select
Application.CutCopyMode = False

' -----------------------------------------------------------------------------
' Non -Core
' "Reports"
Range("T8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-1],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C20)"
Range("T8").Select
Selection.Copy
Range("T8:T12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'-----------------------------------------------------------------------------
' "Reviews"
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-2],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C21)"
Range("U8").Select
Selection.Copy
Range("U8:U12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'-----------------------------------------------------------------------------
' "Training"
Range("V8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-3],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C22)"
Range("V8").Select
Selection.Copy
Range("V8:V12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' ------------------------------------------------------------------------------
' "Management"

Range("W8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-4],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C23)"
Range("W8").Select
Selection.Copy
Range("W8:W12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' -------------------------------------------------------------------------------------
' "System"

Range("X8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-5],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C24)"
Range("X8").Select
Selection.Copy
Range("X8:X12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' --------------------------------------------------------------------------------------
' "Break"
Range("Y8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-6],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C25)"
Range("Y8").Select
Selection.Copy
Range("Y8:Y12").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' Production - Processor-wise details
' "No. of invoice"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-1],Consolidation!R4C67:R15000C67,""<>"")"
Range("C17").Select
Selection.Copy
Range("C17:C38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' -------------------------------------------------------------------------------------
' "Sum of line"
Range("D17").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C32:R15000C32,Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-2])"
Range("D17").Select
Selection.Copy
Range("D17:D38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' --------------------------------------------------------------------------------------
' "Keyed"
Range("E17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-3],Consolidation!R4C34:R15000C34,R16C5)"
Range("E17").Select
Selection.Copy
Range("E17:E38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' ---------------------------------------------------------------------------------------
' "Referred"
Range("F17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-4],Consolidation!R4C34:R15000C34,R16C6)"
Range("F17").Select
Selection.Copy
Range("F17:F38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' ----------------------------------------------------------------------------------------
' "Total Time Taken"
Range("G17").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C38:R15000C38,Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-5])"
Range("G17").Select
Selection.Copy
Range("G17:G38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' -----------------------------------------------------------------------------------------
' "Actual Productivity"
Range("H17").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=0,""0.00"",RC[-5]/RC[-1]/R1C1)"
Range("H17").Select
Selection.Copy
Range("H17:H38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' -----------------------------------------------------------------------------------------
' "Final Productivity"
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-2]/RC[-1]*R1C1)"
Range("J17").Select
Selection.Copy
Range("J17:J38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' ------------------------------------------------------------------------------------------
' "Need to Re-key"
Range("K17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-9],Consolidation!R4C7:R15000C7,R16C11)"
Range("K17").Select
Selection.Copy
Range("K17:K38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' ------------------------------------------------------------------------------------------
' "FPY"
Range("L17").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-9]=0,0,(RC[-9]-RC[-1])/RC[-9])*100"
Range("L17").Select
Selection.Copy
Range("L17:L38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'Quality Processor-wise Details
' "Invoice"
Range("C43").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C3:R15000C3,'P2P Dashboard'!R43C1,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-1])"
Range("C43").Select
Selection.Copy
Range("C43:C64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' -----------------------------------------------------------------------------------------------
' "Sum of line"
Range("C43").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C32:R15000C32,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-2])"
Range("D43").Select
Selection.Copy
Range("D43:D64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' ----------------------------------------------------------------------------------------------------
' "Sum of Line"
Range("D43").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C32:R15000C32,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-2])"
Range("D43").Select
Selection.Copy
Range("D43:D64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' ----------------------------------------------------------------------------------------------------
' "Reviewed"
Range("E43").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C7:R15000C7,'P2P Dashboard'!R42C5,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-3])"
Range("E43").Select
Selection.Copy
Range("E43:E64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' ----------------------------------------------------------------------------------------------------
' "Referred"
Range("F43").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C7:R15000C7,'P2P Dashboard'!R42C6,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-4])"
Range("F43").Select
Selection.Copy
Range("F43:F64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' ----------------------------------------------------------------------------------------------------
' "Need to Re-key"
Range("G43").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C7:R15000C7,'P2P Dashboard'!R42C7,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-5])"
Range("G43").Select
Selection.Copy
Range("G43:G64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' ----------------------------------------------------------------------------------------------------
' "Time Taken"
Range("H43").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C28:R15000C28,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-6])"
Range("H43").Select
Selection.Copy
Range("H43:H64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' ------------------------------------------------------------------------------------------------------
' "Actual Productivity"
Range("I43").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]=0,""0.00"",RC[-6]/RC[-1]/R1C1)"
Range("I43").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=9
Range("I43:I64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'------------------------------------------------------------------------------------------------------
' "Final Productivity"
Range("K43").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,""0.00"",RC[-2]/RC[-1]*R[-41]C[-10])"
Range("K43").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=12
Range("K43:K64").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'SET UP EXCEL OBJECTS
'Set objExcel = objExcelApplication
'Set objWorkbook = objExcelWorkbook(objExcel)
Sheets("P2P Dashboard").Protect Password:="123"


Regards,

Srikanth M :)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
You can get rid of most of the .select lines and just use .clearcontents, .copy, etc.

If you don't want a formula in the cell replace .formulaR1C1 with .value, and put =evaluate( ) around the quotation marks

The paste special parts can be shorter too if you're using the defaults
 
Upvote 0
Your code consists of basically two types of actions... clearing contents of cells and assigning formulas to ranges. I will show you how to simplify an portion of each action and let you apply the simplification to the other sections yourself.

Clear Cell Contents
--------------------------------------
You wrote this...

Range("C8:H12").Select
Range("C8:H12").ClearContents

which can be simplified to this...

Range("C8:H12").ClearContents

You almost never have to select a range in order to operate on it.


Assign Formulas To Ranges
--------------------------------------
You wrote this...

' "Actual Productivity"
Range("H17").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=0,""0.00"",RC[-5]/RC[-1]/R1C1)"
Range("H17").Select
Selection.Copy
Range("H17:H38").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

which can be simplified to this...

' "Actual Productivity"
Range("H17:H38").FormulaR1C1 = "=IF(RC[-5]=0,""0.00"",RC[-5]/RC[-1]/R1C1)"
 
Upvote 0
sshrikanth2,

I created the two worksheets, and after consolidating your code, the following did work.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


As Long as I have not missed something.


You can consolidate your coding from this:

Code: 
Sheets("P2P Dashboard").Unprotect Password:="123"
 Range("C8:H12").Select
 Range("C8:H12").ClearContents
 Range("J8:M12").Select
 Range("J8:M12").ClearContents
 Range("O8:P12").Select
 Range("O8:P12").ClearContents
 Range("T8:Y12").Select
 Range("T8:Y12").ClearContents
 Range("C17:H38").Select
 Range("C17:H38").ClearContents
 Range("J17:L38").Select
 Range("J17:L38").ClearContents
 Range("C43:I64").Select
 Range("C43:I64").ClearContents
 Range("K43:K64").Select
 Range("K43:K64").ClearContents
 ' ---------------------------------
 Range("C8").Select
 ActiveCell.FormulaR1C1 = _
 "=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-1],Consolidation!R4C67:R15000C67,""<>"")"
 Range("C8").Select
 Selection.Copy
 Range("C8:C12").Select
 Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
 SkipBlanks:=False, Transpose:=False
 Application.CutCopyMode = False
' ----------------------------------------------------------------------------------------


To this:


Code: 
Sheets("P2P Dashboard").Unprotect Password:="123"

Range("C8:H12,C8:H12,J8:M12,J8:M12,O8:P12,O8:P12,T8:Y12,T8:Y12,C17:H38,C17:H38,J17:L38,J17:L38,C43:I64,C43:I64,K43:K64,K43:K64").ClearContents
' ---------------------------------

Range("C8:C12").FormulaR1C1 = "=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-1],Consolidation!R4C67:R15000C67,""<>"")"
' ----------------------------------------------------------------------------------------
 
Upvote 0
Hello.

Code: 
Private Sub CommandButton1_Click()
Sheets("P2P Dashboard").Unprotect Password:="123"
Sheets("P2P Dashboard").Range("C8:H12, J8:M12, O8:P12,T8:Y12,C17:H38,J17:L38,C43:I64,K43:K64").Clear
...
...
 
Upvote 0
sshrikanth2,

The new coding I posted above did have some errors/duplicates - they are fixed below.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code: 
Sheets("P2P Dashboard").Unprotect Password:="123"

Range("C8:H12,J8:M12,O8:P12,T8:Y12,C17:H38,J17:L38,C43:I64,K43:K64").ClearContents
' ---------------------------------

Range("C8:C12").FormulaR1C1 = "=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-1],Consolidation!R4C67:R15000C67,""<>"")"
' ----------------------------------------------------------------------------------------
 
Upvote 0
Dear All,

As per your feedback, i tried the coding as below coding but i am getting "run time error - 1004" after the coding highlighted in red. Please advise.

Sheets("P2P Dashboard").Unprotect Password:="123"
'Range("C8:H12").ClearContents
'Range("J8:M12").ClearContents
'Range("O8:P12").ClearContents
'Range("T8:Y12").ClearContents
'Range("C17:H38").ClearContents
'Range("J17:L38").ClearContents
'Range("C43:I64").ClearContents
'Range("K43:K64").ClearContents
Sheets("P2P Dashboard").Range("C8:H12, J8:M12, O8:P12,T8:Y12,C17:H38,J17:L38,C43:I64,K43:K64").ClearContents
' ---------------------------------
Range("C8").Select
Range("C8:C12").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-1],Consolidation!R4C67:R15000C67,""<>"")"
' ----------------------------------------------------------------------------------------
' "Sum of line items"
Range("D8").Select
Range("D8:D12").FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C32:R15000C32,Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-2])"
' ----------------------------------------------------------------------------------------
' "Keyed"
Range("E8").Select
Range("E8:E12").ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-3],Consolidation!R4C7:R15000C7,'P2P Dashboard'!R7C5)"

' --------------------------------------------------------------------------------
' "Referred"
Range("F8").Select
Range("F8:F12").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-4],Consolidation!R4C7:R15000C7,'P2P Dashboard'!R7C6)"
' ---------------------------------------------------------------------------------
' "Total Time Taken"
Range("G8").Select
Range("G8:G12").ActiveCell.FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C38:R15000C38,Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-5])"

' ---------------------------------------------------------------------------------
' "Actual Productivity"
Range("H8").Select
Range("H8:H12").FormulaR1C1 = "=IF(RC[-5]=0,""0.00"",RC[-5]/RC[-1]/R1C1)"

'------------------------------------------------------------------------------
'"Final Productivity"
Range("J8").Select
Range("J8:J12").FormulaR1C1 = "=IF(RC[-2]=0,""0.00"",RC[-2]/RC[-1]*R2C1)"
'------------------------------------------------------------------------------
' "Need to rekey"
Range("K8").Select
Range("K8:K12").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-9],Consolidation!R4C7:R15000C7,'P2P Dashboard'!R7C11)"

'-------------------------------------------------------------------------------
' "Time taken for QC"
Range("L8").Select
Range("L8:L12").FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C28:R15000C28,Consolidation!R4C1:R15000C1,'P2P Dashboard'!RC[-10])"
' ---------------------------------------------------------------------------
' "Actual Productivity"
Range("M8").Select
Range("M8:M12").FormulaR1C1 = "=IF(RC[-10]=0,""0.00"",RC[-10]/RC[-1]/R1C1)"

' ----------------------------------------------------------------------------
' "Final Prodcutivity"
Range("O8").Select
Range("O8:O12").FormulaR1C1 = "=IF(RC[-2]=0,""0.00"",RC[-2]/RC[-1]*R[-6]C[-14])"

' -----------------------------------------------------------------------------
' "FPY"
Range("P8").Select
Range("P8:P12").FormulaR1C1 = "=IF(RC[-13]=0,""0.00"",(RC[-13]-RC[-5])/RC[-13])*100"

' -----------------------------------------------------------------------------
' Non -Core
' "Reports"
Range("T8").Select
Range("T8:T12").FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-1],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C20)"

'-----------------------------------------------------------------------------
' "Reviews"
Range("U8").Select
Range("U8:U12").FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-2],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C21)"

'-----------------------------------------------------------------------------
' "Training"
Range("V8").Select
Range("V8:V12").FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-3],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C22)"

' ------------------------------------------------------------------------------
' "Management"
Range("W8").Select
Range("W8:W12").FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-4],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C23)"

' -------------------------------------------------------------------------------------
' "System"
Range("X8").Select
Range("X8:X12").FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-5],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C24)"

' --------------------------------------------------------------------------------------
' "Break"
Range("Y8").Select
Range("Y8:Y12").FormulaR1C1 = _
"=SUMIFS('Non-Core'!R2C12:R15000C12,'Non-Core'!R2C1:R15000C1,'P2P Dashboard'!RC[-6],'Non-Core'!R2C3:R15000C3,'P2P Dashboard'!R7C25)"

' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' Production - Processor-wise details
' "No. of invoice"
Range("C17").Select
Range("C17:C38").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-1],Consolidation!R4C67:R15000C67,""<>"")"

' -------------------------------------------------------------------------------------
' "Sum of line"
Range("D17").Select
Range("D17:D38").FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C32:R15000C32,Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-2])"

' --------------------------------------------------------------------------------------
' "Keyed"
Range("E17").Select
Range("E17:E38").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-3],Consolidation!R4C34:R15000C34,R16C5)"

' ---------------------------------------------------------------------------------------
' "Referred"
Range("F17").Select
Range("F17:F38").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-4],Consolidation!R4C34:R15000C34,R16C6)"

' ----------------------------------------------------------------------------------------
' "Total Time Taken"
Range("G17").Select
Range("G17:G38").FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C38:R15000C38,Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-5])"

' -----------------------------------------------------------------------------------------
' "Actual Productivity"
Range("H17").Select
Range("H17:H38").FormulaR1C1 = "=IF(RC[-5]=0,""0.00"",RC[-5]/RC[-1]/R1C1)"

' -----------------------------------------------------------------------------------------
' "Final Productivity"
Range("J17").Select
Range("J17:J38").FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-2]/RC[-1]*R1C1)"

' ------------------------------------------------------------------------------------------
' "Need to Re-key"
Range("K17").Select
Range("K17:K38").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C30:R15000C30,'P2P Dashboard'!RC[-9],Consolidation!R4C7:R15000C7,R16C11)"

' ------------------------------------------------------------------------------------------
' "FPY"
Range("L17").Select
Range("L17:L38").FormulaR1C1 = "=IF(RC[-9]=0,0,(RC[-9]-RC[-1])/RC[-9])*100"

' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'Quality Processor-wise Details
' "Invoice"
Range("C43").Select
Range("C43:C64").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C3:R15000C3,'P2P Dashboard'!R43C1,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-1])"

' -----------------------------------------------------------------------------------------------
' "Sum of line"
Range("C43").Select
Range("D43:D64").FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C32:R15000C32,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-2])"

' ----------------------------------------------------------------------------------------------------
' "Reviewed"
Range("E43").Select
Range("E43:E64").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C7:R15000C7,'P2P Dashboard'!R42C5,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-3])"

' ----------------------------------------------------------------------------------------------------
' "Referred"
Range("F43").Select
Range("F43:F64").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C7:R15000C7,'P2P Dashboard'!R42C6,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-4])"

' ----------------------------------------------------------------------------------------------------
' "Need to Re-key"
Range("G43").Select
Range("G43:G64").FormulaR1C1 = _
"=COUNTIFS(Consolidation!R4C7:R15000C7,'P2P Dashboard'!R42C7,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-5])"

' ----------------------------------------------------------------------------------------------------
' "Time Taken"
Range("H43").Select
Range("H43:H64").FormulaR1C1 = _
"=SUMIFS(Consolidation!R4C28:R15000C28,Consolidation!R4C6:R15000C6,'P2P Dashboard'!RC[-6])"

' ------------------------------------------------------------------------------------------------------
' "Actual Productivity"
Range("I43").Select
Range("I43:I64").FormulaR1C1 = "=IF(RC[-6]=0,""0.00"",RC[-6]/RC[-1]/R1C1)"
'------------------------------------------------------------------------------------------------------
' "Final Productivity"
Range("K43").Select
Range("K43:K64").FormulaR1C1 = "=IF(RC[-2]=0,""0.00"",RC[-2]/RC[-1]*R[-41]C[-10])"

'SET UP EXCEL OBJECTS
'Set objExcel = objExcelApplication
'Set objWorkbook = objExcelWorkbook(objExcel)
Sheets("P2P Dashboard").Protect Password:="123"


Regards,

Srikanth M
 
Upvote 0
Hi.

Code: 
Sheets("P2P Dashboard").Range("C8:H12, J8:M12, O8:P12,T8:Y12,C17:H38,J17:L38,C43:I64,K43:K64").Clear
(only "clear")
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
160
Coyotex3
C
P
  • Solved
Counter in For Each loop
Replies
2
Views
197
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
173
pennbowl
P
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
318
kevin9999
K
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
309
Azamamir
A

Forum statistics

Threads
1,215,022
Messages
6,122,721
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top