short cut for add-in

T

tpkelley_no

Board Regular
Joined
Oct 14, 2011
Messages
191
Office Version
  1. 2010
Platform
  1. Windows
I am trying to save a VBA code as an Add-in, and put a button on my home tab. I am doing the following and the button


  1. Save as Excel add-in
  2. File excel options add-in
  3. Manage excel add-in go
  4. Select the add-in file name (Vba Code) and ok
  5. Rite click and select customize the ribbon
  6. New group and rename
  7. Choose commands from macros
I do not see the file called Vba Code. Can someone help me create the short cut? I have excel 2010. here is a copy of the code too
Code: 
Sub Place_in_Table()
'
' delcol Macro
' to del col it exported excel
'
' Keyboard Shortcut: Ctrl+d
'
'  this section will delete rows 1 thr 10 and shift up
    Range("1:1,11:11,2:2,3:3,4:4,5:5,6:6,7:8,9:9,10:10").Select
    Range("A10").Activate
    Selection.Delete Shift:=xlUp
    Range("A1:D1").Select
    Selection.Cut Destination:=Range("B1:E1")
    Range("A:A,F:F,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,S:S").Select
    Range("S1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
    
'for the month of January
Set rngFnd = Range("A:A").Find(1, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 1
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If
    
'for the month of Febuary
    
Set rngFnd = Range("A:A").Find(2, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 2
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If
    
'for the month March
Set rngFnd = Range("A:A").Find(3, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 3
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If
    
'for the month April
Set rngFnd = Range("A:A").Find(4, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 4
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]'for the month May
Set rngFnd = Range("A:A").Find(5, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 5
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If
   
'for the month June
Set rngFnd = Range("A:A").Find(6, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 6
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If
'for the month July
Set rngFnd = Range("A:A").Find(7, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 7
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If
   
 'for the month Auguest
Set rngFnd = Range("A:A").Find(8, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 8
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'for the month September
Set rngFnd = Range("A:A").Find(9, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 9
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If
   
'for the month October
Set rngFnd = Range("A:A").Find(10, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 10
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'for the month November
Set rngFnd = Range("A:A").Find(11, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 11
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'for the month December
Set rngFnd = Range("A:A").Find(12, Cells(1, "A"))
 
If Not rngFnd Is Nothing Then
     
    n = rngFnd.Row
    Do
        Range("A" & n + 1).Value = 12
        n = n + 1
    Loop Until Range("A" & n + 1).Value = ""
End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'delete blank rows in column A
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'move gl name
Range("A1:E1").Select
    Selection.Cut Destination:=Range("J3:N3")
    Range("J3:N3").Select
 'delete emty cells in column D
Columns(4).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
     
'delete close JV row
Dim i As Integer
Dim LR As Integer
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Cells(i, 4).Value = "Closing JV" Then Cells(i, 4).EntireRow.Delete
Next
    
Rows("1:2").Select
    Selection.Insert Shift:=xlDown
    Range("J3:N3").Select
    Selection.Cut Destination:=Range("A1:E1")
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Posting Month"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Invoice Date"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Invoice Amount"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "Vendor Number"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Vendor Name"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Invoice Number"
    Range("F3").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R2C1:R232C6").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Posting Month")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice Date")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor Name")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor Number")
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice Amount")
        .Orientation = xlRowField
        .Position = 5
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
    ActiveSheet.PivotTables("PivotTable1").format xlReport5
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Invoice Amount"), "Sum of Invoice Amount", xlSum
    Range("F3").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Invoice Amount" _
        )
        .NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    End With
    ActiveWindow.SmallScroll Down:=102
End Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Flux_Report()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'only run on my computer
'If Environ("computername") <> "tkellye" Then Exit Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Password section
Dim Password As String
    Password = InputBox("Please enter password below", "Password", "????")
    If Password <> "edit" Then
        MsgBox "Incorrect Password"
        Exit Sub
    Else
    End If
'If statement for months
'JAN
If (Range("B3").Value = Range("C5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur January 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the cloumn with and read the month
    Columns("C:E").ColumnWidth = 23
    Columns("F:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Jan. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the current cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = "=RC[-50]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Jan."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Jan."
End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'FEB
If (Range("B3").Value = Range("F5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur February 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:E").ColumnWidth = 0#
    Columns("F:H").ColumnWidth = 23
    Columns("I:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Feb. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'this will add the current cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = "=RC[-50]+RC[-47]"
'This will copy the fulmia to BA61
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
    Range("BA7:BA59").Select
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Feb."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Feb."
End If
'MAR
If (Range("B3").Value = Range("I5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur March 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:H").ColumnWidth = 0#
    Columns("I:K").ColumnWidth = 23
    Columns("L:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Mar. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the current cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Mar."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Mar."
End If
'APR
If (Range("B3").Value = Range("L5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur Aprial 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:K").ColumnWidth = 0#
    Columns("L:N").ColumnWidth = 23
    Columns("O:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Apr. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the current cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Apr."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Apr."
End If
'MAY
If (Range("B3").Value = Range("O5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur May 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:N").ColumnWidth = 0#
    Columns("O:Q").ColumnWidth = 23
    Columns("R:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur May Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the current cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur May"
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur May"
End If
'JUNE
If (Range("B3").Value = Range("R5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur June 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:Q").ColumnWidth = 0#
    Columns("R:W").ColumnWidth = 23
    Columns("X:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Jun. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the current cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Jun."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Jun."
End If
'JULY
If (Range("B3").Value = Range("X5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur July 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:W").ColumnWidth = 0#
    Columns("X:AB").ColumnWidth = 23
    Columns("AC:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Jul. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the Actual cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur July"
'This will add the current budget amount
Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Jul."
End If
'AUG
If (Range("B3").Value = Range("AC5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur Auguest 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:AB").ColumnWidth = 0#
    Columns("AC:AG").ColumnWidth = 23
    Columns("AH:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Aug. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the Actual cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Aug."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
    Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Aug."
End If
'SEP
If (Range("B3").Value = Range("AH5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur September 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:AG").ColumnWidth = 0#
    Columns("AH:AL").ColumnWidth = 23
    Columns("AM:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Sep. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the Actual cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]+RC[-19]" ' may have an error
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Sep."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]+RC[-19]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
    Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Sep."
End If
'OCT
If (Range("B3").Value = Range("AM5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur October 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:AL").ColumnWidth = 0#
    Columns("AM:AQ").ColumnWidth = 23
    Columns("AR:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Oct. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the Actual cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]+RC[-19]+RC[-14]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Oct."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]+RC[-19]+RC[-14]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
    Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Oct."
End If
'NOV
If (Range("B3").Value = Range("AR5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur November 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:AQ").ColumnWidth = 0#
    Columns("AR:AV").ColumnWidth = 23
    Columns("AW:AZ").ColumnWidth = 0#
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Nov. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'This will add the Actual cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]+RC[-19]+RC[-14]+RC[-9]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Nov."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]+RC[-19]+RC[-14]+RC[-9]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'This will Change the Text in Cell BC6
    Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Nov."
End If
'DEC
If (Range("B3").Value = Range("AW5").Value) Then
' to change the title of the report
Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "Flux General and Administrative Budget thur December 2014"
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("A2:BC2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
End With
'to change the column with
    Columns("C:AV").ColumnWidth = 0#
    Columns("AW:AZ").ColumnWidth = 23
'this will change the title of column BA6
    Range("BA6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Total Cost Thur Dec. Actual"
    With ActiveCell.Characters(Start:=1, Length:=31).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
 'This will add the Actual cost
    Range("BA7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]+RC[-19]+RC[-14]+RC[-9]+RC[-4]"
'This will copy the fulmia to BA61
    Range("BA7").Select
    Selection.AutoFill Destination:=Range("BA7:BA61"), Type:=xlFillDefault
'This will put the text in cell BB6
    Range("BB6").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "2014 Original Budget Thur Dec."
'This will add the current budget amount
    Range("BB7").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-50]+RC[-47]+RC[-44]+RC[-41]+RC[-38]+RC[-35]+RC[-29]+RC[-24]+RC[-19]+RC[-14]+RC[-9]+RC[-4]"
'This will copy the above cell
    Range("BB7").Select
    Selection.AutoFill Destination:=Range("BB7:BB61"), Type:=xlFillDefault
'   Range("BB7:BB59").Select
'This will Change the Text in Cell BC6
    Range("BC6").Select
    ActiveCell.FormulaR1C1 = _
        "2014 Original Variance Thur Dec."
End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
Sub clean_report()
Application.DisplayAlerts = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'To clear the clipboard with VBA
Application.CutCopyMode = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will delete the rows with emty cell in column "N" sheet 1 (clean UP)
        Sheets("sheet1").Range("N3", Sheets("sheet1").Range("N" & Sheets("sheet1").Rows.Count).End(xlUp)).SpecialCells(4).EntireRow.Delete[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'This will clear data in firts two rows in sheet 1 (clean UP)
        Sheets("sheet1").Rows("1:2").EntireRow.ClearContents[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will delete the last two rows in sheet 1 Sheets("Sheet1").Select
    Sheets("sheet1").Select
    Range("a65536").End(xlUp).Select
    Selection.Cells(1, 1).Offset(-1).Resize(2).EntireRow.ClearContents[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will delete the rows with emty cell in column "N" sheet 2 (clean UP)
        Sheets("sheet2").Range("N3", Sheets("sheet2").Range("N" & Sheets("sheet2").Rows.Count).End(xlUp)).SpecialCells(4).EntireRow.Delete[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'This will clear data in firts two rows in sheet 2 (clean UP)
        Sheets("sheet2").Rows("1:2").EntireRow.ClearContents[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will delete the last two rows in sheet 2 Sheets("Sheet2").Select
    Sheets("sheet2").Select
    Range("a65536").End(xlUp).Select
    Selection.Cells(1, 1).Offset(-1).Resize(2).EntireRow.ClearContents[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will copy the forulma to combind cells in cell g i j in sheet 1
    Dim LastRow As Long
    With Sheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        With .Range("H3:H" & LastRow)
            .Formula = "=G3&I3&J3"
            .Value = .Value
       End With
    End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will copy the forulma to combind cells in cell g i j in sheet 2
    Dim LastRowt As Long
    With Sheets("Sheet2")
        LastRowt = .Cells(.Rows.Count, "G").End(xlUp).Row
        With .Range("H3:H" & LastRowt)
            .Formula = "=G3&I3&J3"
            .Value = .Value
       End With
    End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will select the data in sheet 2
    Worksheets("sheet2").Activate
    Range("G3:K3", Range("G3:K3").End(xlDown)).Select
    Selection.Copy[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will past the data to sheet 1
    Worksheets("sheet1").Activate
    Range("g65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlPasteAll[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Delete Duplicates In h Column
   Sheets("sheet1").Select
   Dim x As Long
    Dim LRD As Long
    LRD = Range("H65536").End(xlUp).Row
    For x = LRD To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("h3:h" & x), Range("h" & x).Text) > 1 Then
            Range("h" & x).EntireRow.Delete
        End If
    Next x
'this will insert 4 sheets[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will copy and past columns "G" thu"K" to sheets 3 thu 7
Worksheets("sheet1").Activate
    Range("G2:K2", Range("G2:K2").End(xlDown)).Select
    Selection.Copy[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Worksheets("sheet3").Activate
    Range("g65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlPasteAll[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Worksheets("sheet4").Activate
    Range("g65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlPasteAll[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Worksheets("sheet5").Activate
    Range("g65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlPasteAll[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Worksheets("sheet6").Activate
    Range("g65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlPasteAll[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will remove the copy comand
SendKeys ("{ESC}")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will change the name of the worksheet tabs
Sheets("sheet1").Select
Sheets("sheet1").Name = "original_Cost"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("sheet2").Select
Sheets("sheet2").Name = "New_Cost"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("sheet3").Select
Sheets("sheet3").Name = "Original_Budget"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("sheet4").Select
Sheets("sheet4").Name = "Approved_Changes"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("sheet5").Select
Sheets("sheet5").Name = "Current_Projections"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("sheet6").Select
Sheets("sheet6").Name = "Cost_to_Date"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
'this will copy the data from original and new sheet and match it to the cost code.  the DIM statements will copy the forumla down to the last cell
'this is the vlookup for "Original_Budget"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Original_Budget").Range("N2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-6],original_Cost!R2C8:R" & Sheets("Original_Cost").Cells(2, 8).End(xlDown).Row + 1 & "C12,5,FALSE)),0,VLOOKUP(Original_Budget!RC[-6],original_Cost!R2C8:R" & Sheets("Original_Cost").Cells(2, 8).End(xlDown).Row + 1 & "C12,5,FALSE))"
Sheets("Original_Budget").Range("P2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-8],New_Cost!R3C8:R" & Sheets("New_Cost").Cells(3, 8).End(xlDown).Row + 1 & "C12,5,FALSE)),0,VLOOKUP(Original_Budget!RC[-8],New_Cost!R3C8:R" & Sheets("New_Cost").Cells(3, 8).End(xlDown).Row + 1 & "C12,5,FALSE))"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim Lastof As Long
    With Sheets("Original_Budget")
        Lastof = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("N2").Copy Destination:=.Range("k2:k" & Lastof).Offset(0, 3)
    End With
    
Dim Lastofp As Long
    With Sheets("Original_Budget")
        Lastofp = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("P2").Copy Destination:=.Range("k2:k" & Lastofp).Offset(0, 5)
    End With
    
Sheets("Cost_to_Date").Range("N2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-6],original_Cost!R2C8:R" & Sheets("Original_Cost").Cells(2, 8).End(xlDown).Row + 1 & "C17,10,FALSE)),0,VLOOKUP(Original_Budget!RC[-6],original_Cost!R2C8:R" & Sheets("Original_Cost").Cells(2, 8).End(xlDown).Row + 1 & "C17,10,FALSE))"
Sheets("Cost_to_Date").Range("P2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-8],New_Cost!R3C8:R" & Sheets("New_Cost").Cells(3, 8).End(xlDown).Row + 1 & "C17,10,FALSE)),0,VLOOKUP(Cost_to_Date!RC[-8],New_Cost!R3C8:R" & Sheets("New_Cost").Cells(3, 8).End(xlDown).Row + 1 & "C17,10,FALSE))"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastCtD As Long
    With Sheets("Cost_to_Date")
        LastCtD = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("N2").Copy Destination:=.Range("k2:k" & LastCtD).Offset(0, 3)
    End With
 
Dim LastCtDp As Long
    With Sheets("Cost_to_Date")
        LastCtDp = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("P2").Copy Destination:=.Range("k2:k" & LastCtDp).Offset(0, 5)
    End With
    
Sheets("Current_Projections").Range("N2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-6],original_Cost!R2C8:R" & Sheets("Original_Cost").Cells(2, 8).End(xlDown).Row + 1 & "C15,8,FALSE)),0,VLOOKUP(Original_Budget!RC[-6],original_Cost!R2C8:R" & Sheets("Original_Cost").Cells(2, 8).End(xlDown).Row + 1 & "C15,8,FALSE))"
Sheets("Current_Projections").Range("P2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-8],New_Cost!R3C8:R" & Sheets("New_Cost").Cells(3, 8).End(xlDown).Row + 1 & "C15,8,FALSE)),0,VLOOKUP(Current_Projections!RC[-8],New_Cost!R3C8:R" & Sheets("New_Cost").Cells(3, 8).End(xlDown).Row + 1 & "C15,8,FALSE))"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastCP As Long
    With Sheets("Current_Projections")
        LastCP = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("N2").Copy Destination:=.Range("k2:k" & LastCP).Offset(0, 3)
    End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastCPp As Long
    With Sheets("Current_Projections")
        LastCPp = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("P2").Copy Destination:=.Range("k2:k" & LastCPp).Offset(0, 5)
    End With
    
Sheets("Approved_Changes").Range("N2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-6],original_Cost!R2C8:R" & Sheets("Original_Cost").Cells(2, 8).End(xlDown).Row + 1 & "C13,6,FALSE)),0,VLOOKUP(Original_Budget!RC[-6],original_Cost!R2C8:R" & Sheets("Original_Cost").Cells(2, 8).End(xlDown).Row + 1 & "C13,6,FALSE))"
Sheets("Approved_Changes").Range("P2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-8],New_Cost!R3C8:R" & Sheets("New_Cost").Cells(3, 8).End(xlDown).Row + 1 & "C13,6,FALSE)),0,VLOOKUP(Approved_Changes!RC[-8],New_Cost!R3C8:R" & Sheets("New_Cost").Cells(3, 8).End(xlDown).Row + 1 & "C13,6,FALSE))"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastoAC As Long
    With Sheets("Approved_Changes")
        LastAC = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("N2").Copy Destination:=.Range("k2:k" & LastAC).Offset(0, 3)
    End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastoACp As Long
    With Sheets("Approved_Changes")
        LastACp = .Cells(.Rows.Count, "K").End(xlUp).Row
        .Range("P2").Copy Destination:=.Range("k2:k" & LastACp).Offset(0, 5)
    End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'This will show the difference in comumn "p and n".
Dim LastRowOB As Long
    With Sheets("Original_Budget")
        LastRowOB = .Cells(.Rows.Count, "P").End(xlUp).Row
        With .Range("R2:R" & LastRowOB)
            .Formula = "=P2-N2"
            .Value = .Value
       End With
    End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastRowctd As Long
    With Sheets("Cost_to_Date")
        LastRowctd = .Cells(.Rows.Count, "P").End(xlUp).Row
        With .Range("R2:R" & LastRowctd)
            .Formula = "=P2-N2"
            .Value = .Value
       End With
    End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastRowcp As Long
    With Sheets("Current_Projections")
        LastRowcp = .Cells(.Rows.Count, "P").End(xlUp).Row
        With .Range("R2:R" & LastRowcp)
            .Formula = "=P2-N2"
            .Value = .Value
       End With
    End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim LastRowac As Long
    With Sheets("Approved_Changes")
        LastRowac = .Cells(.Rows.Count, "P").End(xlUp).Row
        With .Range("R2:R" & LastRowac)
            .Formula = "=P2-N2"
            .Value = .Value
       End With
    End With
    
'this will put a heaher on the sheets[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Original_Budget").Select
    Range("G1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    ActiveCell.FormulaR1C1 = "Phase"
    Range("I1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Cost"
    Range("J1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Type"
    Range("K1").Select
    With Selection
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Description"
    Range("N1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Original Cost"
    Range("P1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Revised Cost"
    Range("R1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Difference"
    Range("R2").Select
    Columns("H:H").ColumnWidth = 0
'to set format
    Range("A1,N:N,P:P").Select
    Range("P1").Activate
    ActiveWindow.SmallScroll ToRight:=2
    Range("A1,N:N,P:P,R:R").Select
    Range("R1").Activate
    Selection.Style = "Comma"
'this will sum columns NPR
    
    Dim Rng As Range
    Dim c As Range
    Set Rng = Range("N2:N" & Range("N2").End(xlDown).Row)
    Set c = Range("N2").End(xlDown).Offset(1, 0)
    c.Formula = "=SUM(" & Rng.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Dim RngOB As Range
    Dim cOB As Range
    Set RngOB = Range("P2:P" & Range("P2").End(xlDown).Row)
    Set cOB = Range("P2").End(xlDown).Offset(1, 0)
    cOB.Formula = "=SUM(" & RngOB.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim RngOBR As Range
    Dim cOBR As Range
    Set RngOBR = Range("R2:R" & Range("R2").End(xlDown).Row)
    Set cOBR = Range("R2").End(xlDown).Offset(1, 0)
    cOBR.Formula = "=SUM(" & RngOBR.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Cost_to_Date").Select
    Range("G1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    ActiveCell.FormulaR1C1 = "Phase"
    Range("I1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Cost"
    Range("J1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Type"
    Range("K1").Select
    With Selection
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Description"
    Range("N1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Original Cost"
    Range("P1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Revised Cost"
    Range("R1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Difference"
    Range("R2").Select
    Columns("H:H").ColumnWidth = 0[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'to set format
    Range("A1,N:N,P:P").Select
    Range("P1").Activate
    ActiveWindow.SmallScroll ToRight:=2
    Range("A1,N:N,P:P,R:R").Select
    Range("R1").Activate
    Selection.Style = "Comma"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will sum columns NPR
    
    Dim RngCTDN As Range
    Dim cCTDN As Range
    Set RngCTDN = Range("N2:N" & Range("N2").End(xlDown).Row)
    Set cCTDN = Range("N2").End(xlDown).Offset(1, 0)
    cCTDN.Formula = "=SUM(" & RngCTDN.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Dim RngCTDP As Range
    Dim cCTDP As Range
    Set RngCTDP = Range("P2:P" & Range("P2").End(xlDown).Row)
    Set cCTDP = Range("P2").End(xlDown).Offset(1, 0)
    cCTDP.Formula = "=SUM(" & RngCTDP.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim RngCTDR As Range
    Dim cCTDR As Range
    Set RngCTDR = Range("R2:R" & Range("R2").End(xlDown).Row)
    Set cCTDR = Range("R2").End(xlDown).Offset(1, 0)
    cCTDR.Formula = "=SUM(" & RngCTDR.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Current_Projections").Select
    Range("G1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    ActiveCell.FormulaR1C1 = "Phase"
    Range("I1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Cost"
    Range("J1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Type"
    Range("K1").Select
    With Selection
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Description"
    Range("N1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Original Cost"
    Range("P1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Revised Cost"
    Range("R1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Difference"
    Range("R2").Select
    Columns("H:H").ColumnWidth = 0[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'to set format
    Range("A1,N:N,P:P").Select
    Range("P1").Activate
    ActiveWindow.SmallScroll ToRight:=2
    Range("A1,N:N,P:P,R:R").Select
    Range("R1").Activate
    Selection.Style = "Comma"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'this will sum columns NPR
    
    Dim RngCPN As Range
    Dim cCPN As Range
    Set RngCPN = Range("N2:N" & Range("N2").End(xlDown).Row)
    Set cCPN = Range("N2").End(xlDown).Offset(1, 0)
    cCPN.Formula = "=SUM(" & RngCPN.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Dim RngCPP As Range
    Dim cCPP As Range
    Set RngCPP = Range("P2:P" & Range("P2").End(xlDown).Row)
    Set cCPP = Range("P2").End(xlDown).Offset(1, 0)
    cCPP.Formula = "=SUM(" & RngCPP.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim RngCPR As Range
    Dim cCPR As Range
    Set RngCPR = Range("R2:R" & Range("R2").End(xlDown).Row)
    Set cCPR = Range("R2").End(xlDown).Offset(1, 0)
    cCPR.Formula = "=SUM(" & RngCPR.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Approved_Changes").Select
    Range("G1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    ActiveCell.FormulaR1C1 = "Phase"
    Range("I1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Cost"
    Range("J1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Type"
    Range("K1").Select
    With Selection
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Description"
    Range("N1").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Original Cost"
    Range("P1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Revised Cost"
    Range("R1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Difference"
    Range("R2").Select
    Columns("H:H").ColumnWidth = 0[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'to set format
    Range("A1,N:N,P:P").Select
    Range("P1").Activate
    ActiveWindow.SmallScroll ToRight:=2
    Range("A1,N:N,P:P,R:R").Select
    Range("R1").Activate
    Selection.Style = "Comma"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
'this will sum columns NPR
    
    Dim RngAPN As Range
    Dim cAPN As Range
    Set RngAPN = Range("N2:N" & Range("N2").End(xlDown).Row)
    Set cAPN = Range("N2").End(xlDown).Offset(1, 0)
    cAPN.Formula = "=SUM(" & RngAPN.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Dim RngAPP As Range
    Dim cAPP As Range
    Set RngAPP = Range("P2:P" & Range("P2").End(xlDown).Row)
    Set cAPP = Range("P2").End(xlDown).Offset(1, 0)
    cAPP.Formula = "=SUM(" & RngAPP.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim RngAPR As Range
    Dim cAPR As Range
    Set RngAPR = Range("R2:R" & Range("R2").End(xlDown).Row)
    Set cAPR = Range("R2").End(xlDown).Offset(1, 0)
    cAPR.Formula = "=SUM(" & RngAPR.Address(False, False) & ")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]

 
You must log in or register to reply here.

Similar threads

D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
517
MARK858
MARK858
H
  • Question Question
Create automation VBA code with Power query to load data another workbook
Replies
3
Views
106
hakita
H
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
312
rlv01
R
A
  • Solved
How do I increase efficiency or stop the lag?
Replies
3
Views
66
Aphten
A
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
404
RoryA
RoryA

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