tpkelley_no
Board Regular
- Joined
- Oct 14, 2011
- Messages
- 191
- Office Version
- 2010
- Platform
- 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
- Save as Excel add-in
- File excel options add-in
- Manage excel add-in go
- Select the add-in file name (Vba Code) and ok
- Rite click and select customize the ribbon
- New group and rename
- Choose commands from macros
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]