Private Sub CommandButton1_Click()
BENONI = Date
Sheets("Fiscal Calender").Activate
A = Sheets("Fiscal Calender").Range("W6")
AA = Sheets("Fiscal Calender").Range("X6")
B = Sheets("Fiscal Calender").Range("W7")
BB = Sheets("Fiscal Calender").Range("X7")
C = Sheets("Fiscal Calender").Range("W8")
CC = Sheets("Fiscal Calender").Range("X8")
E = Sheets("Fiscal Calender").Range("W9")
EE = Sheets("Fiscal Calender").Range("X9")
D = InputBox("Closing Dates are Below" _
& vbCr & A & " " & AA _
& vbCr & B & " " & BB _
& vbCr & C & " " & CC _
& vbCr & E & " " & EE, "DATE OF JE PREPARED", [BENONI])
Sheets("ADI").Activate
Sheets("ADI").Range("J11").Select
ActiveCell.Value = D
'DATE ENTERED AND SECOND MACRO RUNS
Sheets("Data").Activate
Sheets("data").Range("A2").Select
Selection.Copy
Sheets("ADI").Activate
Sheets("ADI").Range("J10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Activate
Sheets("data").Range("B2").Select
Selection.Copy
Sheets("ADI").Activate
Sheets("ADI").Range("J12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("ADI").Range("J13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("ADI").Range("J14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("ADI").Range("J15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Activate
Sheets("data").Range("F2").Select
Selection.Copy
Sheets("ADI").Activate
Sheets("ADI").Range("J16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'NEXT
Sheets("Data").Activate
Sheets("data").Range("G2:R2").Select
Sheets("data").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ADI").Activate
Sheets("ADI").Range("C21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'next
Sheets("Data").Activate
Sheets("data").Range("s2").Select
Sheets("data").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ADI").Activate
Sheets("ADI").Range("t21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'next
Sheets("Data").Activate
Sheets("data").Range("t2").Select
Sheets("data").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ADI").Activate
Sheets("ADI").Range("u21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'next
Sheets("Data").Activate
Sheets("data").Range("u2").Select
Sheets("data").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ADI").Activate
Sheets("ADI").Range("ab21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Private Sub CommandButton2_Click()
Sheets("data").Activate
RowCount = Application.WorksheetFunction.CountA(Sheets("data").Range("a:a"))
MsgBox (RowCount - 1)
Sheets("ADI").Activate
Sheets("ADI").Range("C22").Select
RowCount = Application.WorksheetFunction.CountA(Sheets("data").Range("A:A"))
Sheets("ADI").Range("C22").Resize(RowCount, 1).EntireRow.Insert
MsgBox (RowCount - 1 & " " & " " & ("Rows Are Inserted in ADI Sheet"))
Sheets("MAIN").Activate
End Sub
Private Sub CommandButton3_Click() 'MACRO TO SAVE THE FILE IN THE LOCATION
Dim wb As Workbook
MSG2 = MsgBox("Are You Sure You Want to SAVE the ADI Sheet ", vbYesNo, "To Save: YES Else NO")
If MSG2 = vbYes Then
spath = Sheets("MAIN").Range("K14")
rn = Sheets("adi").Range("J13")
A = InputBox("Specify the Month Here to add to File Name")
Worksheets("Adi").Copy
Set wb = ActiveWorkbook
wb.SaveAs Filename:=(spath & rn & " " & "-" & A & ".xls")
MsgBox (" File saved in" _
& vbCr & " " & spath)
Else
MsgBox "File Not Saved"
End If
End Sub
Private Sub CommandButton4_Click() 'MACRO TO DELETE ROWS
Dim FR As Long, LR As Long
MSG1 = MsgBox("Are You Sure You Want to Delete the Rows ", vbYesNo, "To Delete: YES Else NO")
If MSG1 = vbYes Then
Sheets("ADI").Activate
With Sheets("ADI")
FR = 21
LR = .Range("C" & Rows.Count).End(xlUp).Row
.Rows(FR & ":" & LR).Delete
End With
MsgBox ("Rows Deleted")
Else
MsgBox "You Have Cancelled The Action"
End If
Sheets("MAIN").Activate
End Sub
Private Sub CommandButton5_Click()
Sheets("DATA").Activate
ActiveSheet.Range("a:xfd").Select
Selection.Delete Shift:=xlUp
Sheets("DATA").Range("A1").Select
Sheets("MAIN").Activate
End Sub
Private Sub CommandButton6_Click()
Sheets("ADI").Select
ActiveSheet.Range("U20:u1048576").Select
Selection.Replace What:=".", Replacement:="\.", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
MsgBox ("Project Numbers having . replaced with \.")
Sheets("main").Activate
End Sub
Bookmarks