Sub Macro12()
Application.ScreenUpdating = False
Dim EB70 As String
Dim Sourcewb As Workbook
Dim Sourcewb2 As Workbook
Dim Sourcews As Worksheet
MsgBox "Please select Eastbay Local 70 Adjustments file.", vbOKOnly
Dim Dbox As FileDialog
Dim File_Path As String
Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
Dbox.Title = "Choose and Open" & FileType
Dbox.Filters.Clear
Dbox.Show
If Dbox.SelectedItems.count = 1 Then File_Path = Dbox.SelectedItems(1)
Set Sourcewb = Workbooks.Open(Filename:=File_Path)
Set Sourcews = Sourcewb.Sheets(1)
ThisWorkbook.Activate
Sheets("Split Request").Select
Sheets.Add After:=ActiveSheet
Sheets("Sheet12").Name = "EB Adj Cons"
Range("A1").FormulaR1C1 = "Account"
Range("B1").FormulaR1C1 = "Bill period"
Range("C1").FormulaR1C1 = "Adjustments"
Range("E1").FormulaR1C1 = "Consolidated Account"
Range("E2").FormulaR1C1 = "129542"
Range("E3").FormulaR1C1 = "129543"
Range("E4").FormulaR1C1 = "129544"
Range("E6").FormulaR1C1 = "Cons Other Lcl Acct"
Range("E7").FormulaR1C1 = "129542"
Range("E8").FormulaR1C1 = "129543"
Range("E9").FormulaR1C1 = "129544"
Range("E11").FormulaR1C1 = "Grand Totals"
Range("E12").FormulaR1C1 = "129542"
Range("E13").FormulaR1C1 = "129543"
Range("E14").FormulaR1C1 = "129544"
Range("F1").FormulaR1C1 = "Consolidated Totals"
Range("F6").FormulaR1C1 = "Cons Totals"
Range("H1").FormulaR1C1 = "Account"
Range("I1").FormulaR1C1 = "Bill Period"
Range("J1").FormulaR1C1 = "Adjustments"
Range("A:A,H:H").ColumnWidth = 8
Range("B:B,I:I").ColumnWidth = 10
Range("C:C,J:J").ColumnWidth = 13
Range("D:D,G:G").ColumnWidth = 2
Range("E:F").ColumnWidth = 20
Sourcewb.Activate
Sheets(1).Select
Dim lr As Long
Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").FormulaR1C1 = "=CONCAT(RC[-2],""/"",RC[-1])"
Range("D2").AutoFill Destination:=Range("D2:D10000")
lr = Cells(Rows.count, "A").End(xlUp).Row
Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
ThisWorkbook.Activate
Sheets("EB Adj Cons").Select
ActiveSheet.Range("A2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sourcewb.Activate
lr = Cells(Rows.count, "A").End(xlUp).Row
Range("$D$2:$D$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
ThisWorkbook.Activate
Sheets("EB Adj Cons").Select
ActiveSheet.Range("B2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sourcewb.Activate
lr = Cells(Rows.count, "A").End(xlUp).Row
Range("$L$2:$L$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
ThisWorkbook.Activate
Sheets("EB Adj Cons").Select
ActiveSheet.Range("C2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("F2").Formula2R1C1 = "=SUM(IF(EXACT(C[-5],RC[-1]),C[-3],""""))"
Range("F2").AutoFill Destination:=Range("F2:F4"), Type:=xlFillDefault
MsgBox "Please select Eastbay Other Locals Adjustments file.", vbOKOnly
Dim Dbox2 As FileDialog
Dim File_Path2 As String
Set Dbox2 = Application.FileDialog(msoFileDialogFilePicker)
Dbox2.Title = "Choose and Open" & FileType
Dbox2.Filters.Clear
Dbox2.Show
If Dbox2.SelectedItems.count = 1 Then File_Path2 = Dbox2.SelectedItems(1)
Set Sourcewb2 = Workbooks.Open(Filename:=File_Path2)
Set Sourcews2 = Sourcewb2.Sheets(1)
Sourcewb2.Activate
Sheets(1).Select
Dim lr2 As Long
Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").FormulaR1C1 = "=CONCAT(RC[-2],""/"",RC[-1])"
Range("D2").AutoFill Destination:=Range("D2:D10000")
lr2 = Cells(Rows.count, "A").End(xlUp).Row
Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
ThisWorkbook.Activate
Sheets("EB Adj Cons").Select
ActiveSheet.Range("H2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sourcewb2.Activate
lr2 = Cells(Rows.count, "A").End(xlUp).Row
Range("$D$2:$D$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
ThisWorkbook.Activate
Sheets("EB Adj Cons").Select
ActiveSheet.Range("I2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sourcewb2.Activate
lr2 = Cells(Rows.count, "A").End(xlUp).Row
Range("$L$2:$L$" & Cells(Rows.count, "A").End(xlUp).Row).Copy
ThisWorkbook.Activate
Sheets("EB Adj Cons").Select
ActiveSheet.Range("J2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("F7").Formula2R1C1 = "=SUM(IF(EXACT(C[2],RC[-1]),C[4],""""))"
Range("F7").AutoFill Destination:=Range("F7:F9"), Type:=xlFillDefault
Range("F12").FormulaR1C1 = "=SUM(R[-10]C,R[-5]C)"
Range("F13").FormulaR1C1 = "=SUM(R[-10]C,R[-5]C)"
Range("F14").FormulaR1C1 = "=SUM(R[-10]C,R[-5]C)"
Sheets("Main").Select
Range("H16").FormulaR1C1 = "='EB Adj Cons'!R[-14]C[-2]"
Range("H17").FormulaR1C1 = "='EB Adj Cons'!R[-10]C[-2]"
Range("H19").FormulaR1C1 = "='EB Adj Cons'!R[-16]C[-2]"
Range("H20").FormulaR1C1 = "='EB Adj Cons'!R[-12]C[-2]"
Range("H21").FormulaR1C1 = "='EB Adj Cons'!R[-17]C[-2]"
Sheets("EB Adj Cons").Select
Range("K:K,O:O,S:S").ColumnWidth = 2
Range("L:L,P:P").ColumnWidth = 8
Range("M:M,Q:Q").ColumnWidth = 10
Range("N:N,R:R").ColumnWidth = 13
Range("L1").FormulaR1C1 = "Account"
Range("M1").FormulaR1C1 = "Bill Period"
Range("N1").FormulaR1C1 = "Adjustment"
Range("P1").FormulaR1C1 = "Account"
Range("Q1").FormulaR1C1 = "Bill Period"
Range("R1").FormulaR1C1 = "Adjustment"
Dim lr3 As Long
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("L2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "mm/yyyy"
ActiveSheet.Range("$L$1:$M$75000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
lr3 = Cells(Rows.count, "L").End(xlUp).Row
Range("N2").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-11],C[-13],RC[-2],C[-12],RC[-1])"
Range("N2").AutoFill Destination:=Range("N2:N" & Range("L" & Rows.count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Dim lr4 As Long
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("P2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("Q2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "mm/yyyy"
ActiveSheet.Range("$P$1:$Q$75000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
lr4 = Cells(Rows.count, "P").End(xlUp).Row
Range("R2").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-8],C[-10],RC[-2],C[-9],RC[-1])"
Range("R2").AutoFill Destination:=Range("R2:R" & Range("P" & Rows.count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Dim lr5 As Long
Range("L2:N" & lr3).Copy
Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("EB Adj Cons").Select
Range("P2:R" & lr4).Copy
Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Main").Select
Range("F15").Copy
Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Main").Select
Range("G15").Copy
Sheets("Split Request").Range("C" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Main").Select
Range("F18").Copy
Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Main").Select
Range("G18").Copy
Sheets("Split Request").Range("C" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Main").Select
Range("F21").Copy
Sheets("Split Request").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Main").Select
Range("G21").Copy
Sheets("Split Request").Range("C" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
MsgBox "Make sure to add current bill period to last entries on the Split Request.", vbOKOnly
Sheets.Add After:=ActiveSheet
Sheets("Sheet13").Name = "Eastbay Adjustments"
Sheets("Eastbay Adjustments").Select
Dim lr6 As Long
Dim lr7 As Long
Sourcewb.Activate
Columns("D").Delete
lr6 = Range("A" & Rows.count).End(xlUp).Row
ActiveSheet.Range("$A$1:$P$" & lr6).AutoFilter Field:=11, Criteria1:="<0"
Range("$A$1:$P$" & Cells(Rows.count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Activate
Sheets("Eastbay Adjustments").Select
Range("A1").Select
Cells(Rows.count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sourcewb.Activate
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.count - 1).Rows.Delete
Application.DisplayAlerts = True
Range("K:K").AutoFilter
Application.DisplayAlerts = False
MsgBox "Save as 'Eastbay_70_Adj' to be sent to Operations.", vbOKOnly
Dim File_Name As String
File_Name = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=File_Name & "xlsx"
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Application.DisplayAlerts = True
Dim lr8 As Long
Dim lr9 As Long
Sourcewb2.Activate
Columns("D").Delete
lr8 = Cells(Rows.count, "A").End(xlUp).Row
Columns("K:K").AutoFilter
ActiveSheet.Range("$A$1:$P$" & lr8).AutoFilter Field:=11, Criteria1:="<0"
Range("$A$1:$P$" & Cells(Rows.count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Activate
Sheets("Eastbay Adjustments").Select
lr9 = Cells(Rows.count, "A").End(xlUp).Row
Cells(Rows.count, 1).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sourcewb2.Activate
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.count - 1).Rows.Delete
Application.DisplayAlerts = True
Range("K:K").AutoFilter
Application.DisplayAlerts = False
MsgBox "Save as 'Eastbay_287_Adj' to be sent to Operations.", vbOKOnly
Dim File_Name2 As String
File_Name2 = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=File_Name2 & "xlsx"
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Application.DisplayAlerts = True
Dim EBws As Worksheet
Dim MA As Workbook
For Each MA In Application.Workbooks
If MA.Name Like "*Manual Adjustments*" Then
MA.Activate
Exit For
End If
Next MA
ThisWorkbook.Activate
Sheets("Eastbay Adjustments").Select
Sheets("Eastbay Adjustments").Move After:=Workbooks(2).Sheets(1)
MA.Activate
ActiveWorkbook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub