Working on Excel sheets in same Workbook. Getting values from each sheet and consolidating and placing it under one sheet.

'3/8/2016

Attribute VB_Name = "Module1"
Sub ConsolidatedApps()
Dim Workbookname, maxrowcount, Consolidatesheetname, Appname, strX, Vp, Dir
'Workbook name
Dim Test_Annual, Test_Net, Test_Inv, TC_Annual, TC_Net, TC_Inv, O_Annual, O_Net, O_Inv
Workbookname = ActiveWorkbook.Name
Consolidatesheetname = "Consolidated Apps"

'Activate specific application tab and get the values
Workbooks(Workbookname).Sheets(Consolidatesheetname).Activate

maxrowcount = ActiveSheet.UsedRange.Rows.Count

For i = 3 To maxrowcount - 1

'strX = UCase(ActiveSheet.Cells(i, 1).Value)
'str1st = Left(strX, 1)
'strRest = Right(strX, Len(strX) - 1)
'Appname = UCase(str1st) & LCase(strRest)
Appname = ActiveSheet.Cells(i, 1).Value
'Go to respective app tabs and retreive the values

If (Appname = "ABACUS") Then
Appname = "Abacus"
End If

If (Appname = "Oracle Financial Analytics Business Intelligence (BIEE,FABI) & Oracle Reporting") Then
Appname = "OBIEE,FABI"
End If
If (Appname = "Finance Regulatory Reporting Data Warehouse deployment (OFSAA)") Then
Appname = "OFSAA"
End If
If (Appname = "GRC Archer - GRM RDT & Exceptions Mgmt") Then
Appname = "GRC Archer - GRM RDT"
End If
If (Appname = "Asset Backed Securitization (ABS) Re-Architecture") Then
Appname = "Asset Backed Securitization"
End If

If (Appname = "Corporate Asset Liability Management / LCR / TDS") Then
Appname = "Corporate Asset Liability "
End If

If (Appname = "GRE APPROPRIATION REQUEST (GRE AR)") Then
Appname = "GRE AR"
End If

If (Appname = "Integrated Workplace Management System (IWMS)") Then
Appname = "IWMS"
End If

If (Appname = "Basel Analytical & Reporting Environment") Then
Appname = "Basel Analytical & Reporting "
End If

If (Appname = "BI&DW-Rewards & Rebates Applications") Then
Appname = "BI&DW-Rewards"
End If

If (Appname = "Basel II ODS Warehouse / Basel II RWA-C") Then
Appname = "Basel_ODS_Warehouse & RWA-C"
End If

Workbooks(Workbookname).Sheets(Appname).Activate
Vp = Workbooks(Workbookname).Sheets(Appname).Cells(3, 4).Value
Dir = Workbooks(Workbookname).Sheets(Appname).Cells(4, 4).Value
Test_Annual = Workbooks(Workbookname).Sheets(Appname).Cells(30, 20).Value
Test_Net = Workbooks(Workbookname).Sheets(Appname).Cells(30, 21).Value
Test_Inv = Workbooks(Workbookname).Sheets(Appname).Cells(30, 4).Value

TC_Annual = Workbooks(Workbookname).Sheets(Appname).Cells(59, 17).Value + Workbooks(Workbookname).Sheets(Appname).Cells(59, 18).Value
TC_Net = Workbooks(Workbookname).Sheets(Appname).Cells(59, 19).Value
TC_Inv = Workbooks(Workbookname).Sheets(Appname).Cells(59, 4).Value
     
O_Annual = Workbooks(Workbookname).Sheets(Appname).Cells(93, 4).Value + Workbooks(Workbookname).Sheets(Appname).Cells(93, 10).Value + Workbooks(Workbookname).Sheets(Appname).Cells(92, 16).Value
O_Net = O_Annual - O_Inv 'Workbooks(Workbookname).Sheets(Appname).Cells(93, 16).Value
O_Inv = Workbooks(Workbookname).Sheets(Appname).Cells(92, 4).Value + Workbooks(Workbookname).Sheets(Appname).Cells(92, 10).Value + Workbooks(Workbookname).Sheets(Appname).Cells(92, 17).Value

Workbooks(Workbookname).Sheets(Consolidatesheetname).Activate

'Insert the values

ActiveSheet.Cells(i, 3).Value = Vp
ActiveSheet.Cells(i, 4).Value = Dir
ActiveSheet.Cells(i, 5).Value = Test_Annual
ActiveSheet.Cells(i, 6).Value = Test_Inv
ActiveSheet.Cells(i, 7).Value = Test_Net
ActiveSheet.Cells(i, 8).Value = TC_Annual
ActiveSheet.Cells(i, 10).Value = TC_Net
ActiveSheet.Cells(i, 9).Value = TC_Inv
ActiveSheet.Cells(i, 11).Value = O_Annual
ActiveSheet.Cells(i, 13).Value = O_Net
ActiveSheet.Cells(i, 12).Value = O_Inv


Next

End Sub