VBA Smaller Code 5

Z

ziad alsayed

Well-known Member
Joined
Jul 17, 2010
Messages
665
dear all

the below code is working perfectly, i just need to reduce it.

appreciate any help


Code: 
Sub MoveSalesToSheets()
Dim wbo As Workbook
Set wbo = ActiveWorkbook
Set wsd = wbo.Worksheets("Data")
Set wsphc = wbo.Worksheets("PHC")
Set wsapapa = wbo.Worksheets("Apapa")
Set wsvi = wbo.Worksheets("VI")
Set wskano = wbo.Worksheets("Kano")
Set wsikeja = wbo.Worksheets("Ikeja")
wsphc.Select
If Cells(2, 1) = "Jan" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(4, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(4, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(4, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(4, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(4, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(4, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
        Range(Cells(4, 2), Cells(15, 2)).Select
        With Selection
        .Value = .Value
        End With
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(4, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(4, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(4, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(4, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(4, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(4, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(4, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(4, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(4, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(4, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(4, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(4, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(4, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(4, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(4, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(4, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(4, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(4, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(4, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(4, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(4, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(4, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
'wsphc.Select
ElseIf Cells(2, 1) = "Feb" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(5, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(5, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(5, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(5, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(5, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(5, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(5, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(5, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(5, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(5, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(5, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(5, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(5, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(5, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(5, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(5, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(5, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(5, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(5, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(5, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(5, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(5, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(5, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(5, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(5, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(5, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(5, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(5, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Mar" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(6, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(6, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(6, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(6, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(6, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(6, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(6, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(6, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(6, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(6, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(6, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(6, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(6, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(6, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(6, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(6, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(6, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(6, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(6, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(6, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(6, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(6, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(6, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(6, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(6, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(6, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(6, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(6, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Apr" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(7, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(7, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(7, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(7, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(7, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(7, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(7, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(7, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(7, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(7, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(7, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(7, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(7, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(7, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(7, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(7, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(7, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(7, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(7, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(7, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(7, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(7, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(7, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(7, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(7, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(7, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(7, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(7, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "May" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(8, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(8, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(8, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(8, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(8, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(8, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(8, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(8, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(8, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(8, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(8, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(8, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(8, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(8, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(8, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(8, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(8, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(8, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(8, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(8, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(8, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(8, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(8, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(8, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(8, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(8, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(8, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(8, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Jun" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(9, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(9, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(9, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(9, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(9, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(9, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(9, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(9, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(9, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(9, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(9, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(9, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(9, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(9, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(9, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(9, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(9, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(9, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(9, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(9, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(9, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(9, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(9, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(9, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(9, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(9, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(9, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(9, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Jul" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(10, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(10, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(10, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(10, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(10, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(10, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(10, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(10, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(10, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(10, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(10, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(10, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(10, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(10, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(10, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(10, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(10, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(10, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(10, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(10, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(10, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(10, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(10, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(10, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(10, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(10, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(10, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(10, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Aug" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(11, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(11, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(11, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(11, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(11, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(11, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(11, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(11, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(11, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(11, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(11, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(11, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(11, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(11, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(11, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(11, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(11, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(11, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(11, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(11, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(11, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(11, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(11, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(11, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(11, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(11, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(11, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(11, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Sep" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(12, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(12, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(12, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(12, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(12, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(12, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(12, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(12, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(12, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(12, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(12, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(12, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(12, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(12, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(12, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(12, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(12, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(12, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(12, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(12, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(12, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(12, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(12, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(12, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(12, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(12, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(12, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(12, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Oct" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(13, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(13, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(13, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(13, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(13, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(13, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(13, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(13, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(13, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(13, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(13, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(13, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(13, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(13, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(13, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(13, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(13, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(13, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(13, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(13, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(13, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(13, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(13, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(13, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(13, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(13, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(13, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(13, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Nov" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(14, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(14, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(14, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(14, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(14, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(14, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(14, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(14, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(14, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(14, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(14, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(14, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(14, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(14, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(14, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(14, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(14, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(14, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(14, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(14, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(14, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(14, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(14, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(14, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(14, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(14, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(14, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(14, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
        
ElseIf Cells(2, 1) = "Dec" Then
'Add Data to PHC Sheet
    ' select PHC Sheet
        wsphc.Select
    ' sheet PHC, Solomon sales
        Cells(15, 2).FormulaR1C1 = "=SUMIF(Data!C,PHC!R2C2,Data!C[1])"
    ' sheet PHC, Emeka sales
        Cells(15, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],PHC!R2C5,Data!C[-2])"
    ' sheet PHC, Innocent sales
        Cells(15, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],PHC!R2C8,Data!C[-5])"
    ' sheet PHC, Doris sales
        Cells(15, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],PHC!R2C11,Data!C[-8])"
    ' sheet PHC, Spencer sales
        Cells(15, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],PHC!R2C14,Data!C[-11])"
    'sheet PHC Johnnie sales
        Cells(15, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],PHC!R2C17,Data!C[-14])"
     
'add data to Apapa sheet
        wsapapa.Select
    'sheet Apapa Blessing sales
        Cells(15, 2).FormulaR1C1 = "=SUMIF(Data!C,Apapa!R2C2,Data!C[1])"
    ' sheet Apapa Angela Sales
        Cells(15, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Apapa!R2C5,Data!C[-2])"
    'sheet Apapa Helen Sales
        Cells(15, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Apapa!R2C8,Data!C[-5])"
    'sheet Apapa Patrick Sales
        Cells(15, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Apapa!R2C11,Data!C[-8])"
    'sheet apapa Wesley Sales
        Cells(15, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Apapa!R2C14,Data!C[-11])"
    ' sheet Apapa Linda Sales
        Cells(15, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Apapa!R2C17,Data!C[-14])"
       
'add data to VI sheet
        wsvi.Select
    'sheet VI haytham sales
        Cells(15, 2).FormulaR1C1 = "=SUMIF(Data!C,VI!R2C2,Data!C[1])"
    ' sheet VI Osaratin Sales
        Cells(15, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],VI!R2C5,Data!C[-2])"
    'sheet VI Romoke Sales
        Cells(15, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],VI!R2C8,Data!C[-5])"
    'sheet VI Dorothy Sales
        Cells(15, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],VI!R2C11,Data!C[-8])"
    'sheet VI Jhon Sales
        Cells(15, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],VI!R2C14,Data!C[-11])"
       
'add data to Kano Sheet
        wskano.Select
    'sheet Kano Abdulkadir sales
        Cells(15, 2).FormulaR1C1 = "=SUMIF(Data!C,Kano!R2C2,Data!C[1])"
    ' sheet Kano Sani Nasiru Sales
        Cells(15, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Kano!R2C5,Data!C[-2])"
    'sheet Kano Sani Adamu Sales
        Cells(15, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Kano!R2C8,Data!C[-5])"
    'sheet Kano Zahra Sales
        Cells(15, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Kano!R2C11,Data!C[-8])"
      
' add Data to Ikeja Sheet
       wsikeja.Select
    ' sheet Ikeja, Jide  sales
        Cells(15, 2).FormulaR1C1 = "=SUMIF(Data!C,Ikeja!R2C2,Data!C[1])"
    ' sheet Ikeja, Margaret sales
        Cells(15, 5).FormulaR1C1 = "=SUMIF(Data!C[-3],Ikeja!R2C5,Data!C[-2])"
    ' sheet Ikeja, Folake sales
        Cells(15, 8).FormulaR1C1 = "=SUMIF(Data!C[-6],Ikeja!R2C8,Data!C[-5])"
    ' sheet Ikeja, Sabitu sales
        Cells(15, 11).FormulaR1C1 = "=SUMIF(Data!C[-9],Ikeja!R2C11,Data!C[-8])"
    ' sheet Ikeja, Olawaseum sales
        Cells(15, 14).FormulaR1C1 = "=SUMIF(Data!C[-12],Ikeja!R2C14,Data!C[-11])"
    'sheet Ikeja, Mohammed Khobaiz sales
        Cells(15, 17).FormulaR1C1 = "=SUMIF(Data!C[-15],Ikeja!R2C17,Data!C[-14])"
    'sheet Ikeja Patrick Sales
        Cells(15, 20).FormulaR1C1 = "=SUMIF(Data!C[-18],Ikeja!R2C20,Data!C[-17])"
End If
End Sub
 
Sort by date Sort by votes
ziad alsayed said:
was about 900 rows and now it is about 20 rows.:laugh:
Click to expand...
Some more for you to chew on, the code could be shortened a bit more, however this will tend to obfuscate the code, making it difficult for a human to understand, just for the sake a saving a few lines of code. There comes a point where elegance/shortness of code becomes more expensive than the (perhaps) time saved running the code.

So you could have this:
Code: 
Sub blah()
Dim MonthOffset, sht, lastCol, colNo, x
On Error Resume Next
MonthOffset = Month(DateValue(Sheets("PHC").Range("A2").Value & ", 2000")) + 3
On Error GoTo 0
If IsEmpty(MonthOffset) Then
  MsgBox "An incorrect month is entered in cell A2", vbOKOnly, "Error"
  Exit Sub
Else
 [COLOR=Red] x = Array("PHC", 17, "Apapa", 17, "VI", 14, "Kano", 11, "Ikeja", 20)[/COLOR]
  For Each sht In Sheets(Array("PHC", "Apapa", "VI", "Kano", "Ikeja"))
  [COLOR=Red]  lastCol = x(Application.Match(sht.Name, x, 0))[/COLOR]
    For colNo = 2 To lastCol Step 3
      sht.Cells(MonthOffset, colNo).FormulaR1C1 = "=SUMIF(Data!C2,R2C,Data!C3)"
    Next colNo
  Next sht
End If
End Sub
which has removed the Select Case part:
Code: 
Select Case sht.Name
      Case "Apapa", "PHC": lastCol = 17
      Case "VI": lastCol = 14
      Case "Kano": lastCol = 11
      Case "Ikeja": lastCol = 20
    End Select
and replaced it with just 2 lines:
Code: 
  x = Array("PHC", 17, "Apapa", 17, "VI", 14, "Kano", 11, "Ikeja", 20)
    lastCol = x(Application.Match(sht.Name, x, 0))
which does make it easier to add/adjust sheet names and their last columns. But this is academic. I feel the last column to have formulae added can be calculated from the sheet itself, perhaps the headers row has a rightmost cell with something in. It's easy to determine the rightmost cell with anything in it (one line): Let's say the rightmost cell in the header row (row 1) on each sheet only has something in it if there are formulae to added in the column below. And, by definition, the cells to the right of it are all empty, then:
Code: 
sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
will return that column no. so your code might change to:
Code: 
Sub blah()
Dim MonthOffset, sht, lastCol, colNo
On Error Resume Next
MonthOffset = Month(DateValue(Sheets("PHC").Range("A2").Value & ", 2000")) + 3
On Error GoTo 0
If IsEmpty(MonthOffset) Then
  MsgBox "An incorrect month is entered in cell A2", vbOKOnly, "Error"
  Exit Sub
Else
  For Each sht In Sheets(Array("PHC", "Apapa", "VI", "Kano", "Ikeja"))
   [COLOR=Red] lastCol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column[/COLOR]
    For colNo = 2 To lastCol Step 3
      sht.Cells(MonthOffset, colNo).FormulaR1C1 = "=SUMIF(Data!C2,R2C,Data!C3)"
    Next colNo
  Next sht
End If
End Sub
which means we lose the
x= Array(~
line and the
lastCol = x(Application.Match~
line, what's more, you reduce the amount of hard-coded information that needs to be in the code itself, making it more flexible, and theoretically, lastCol being calculated whatever sheet you throw at it.

At the moment, the sheet names are hard-coded, but there is a technique where you can lose that too. This would mean if you added or deleted sheets it could still work, without alteration. You'd have to add a sheet called First, to the left of the first sheet you want to include in the processing, and another sheet called Last to the right of the last sheet you want to process, then hide both of these new sheets. The following code will process all the sheets between those two hidden sheets. If you want to add sheets to be processed, just include them between these two hidden sheets.
Code: 
Sub blah2()
Dim MonthOffset, sht, lastCol, colNo, F, L, i
F = Sheets("First").Index
L = Sheets("Last").Index
On Error Resume Next
MonthOffset = Month(DateValue([COLOR=Red]Sheets(F + 1)[/COLOR].Range("A2").Value & ", 2000")) + 3
On Error GoTo 0
If IsEmpty(MonthOffset) Then
  MsgBox "An incorrect month is entered in cell A2", vbOKOnly, "Error"
  [COLOR=Red]Application.Goto Sheets(F + 1).Range("A2")[/COLOR]
  Exit Sub
Else
[COLOR=Red]  For i = F + 1 To L - 1
    Set sht = Sheets(i)[/COLOR]
    lastCol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
    For colNo = 2 To lastCol Step 3
      sht.Cells(MonthOffset, colNo).FormulaR1C1 = "=SUMIF(Data!C2,R2C,Data!C3)"
    Next colNo
  [COLOR=Red]Next i[/COLOR]
End If
End Sub
The month to process has to be in cell A2 of the leftmost sheet of the group, if there's an error there I'va added a line to select the cell that needs a correct month putting in.
So now, the only hard code is the sheet names First and Last, which should change anyway.

If ithe header row can't be used to determine the last column, surely there will be something else on the sheet to do so?
 
Last edited:
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
dear p45cal

this is something else, honeslty speaking you transfer it to a general code that i can add or remove sheets, add and remove salesman.
you said it is "elegance", i will say it is class, chic, panache,spirit

i just amended to fit my needs, as you mentioned i have some other calculation to the right side.

thanks for being a coach, to be more preicse a mentor.

:beerchug:




Code: 
Sub blah2()
Dim MonthOffset, sht, lastCol, colNo, x, F, L, i
F = Sheets("First").Index
L = Sheets("Last").Index
On Error Resume Next
MonthOffset = Month(DateValue(Sheets(F + 1).Range("[COLOR=red]A1[/COLOR]").Value & ", 2000")) +[COLOR=red] 2
[/COLOR]On Error GoTo 0
If IsEmpty(MonthOffset) Then
  MsgBox "An incorrect month is entered in cell A1", vbOKOnly, "Error"
  Application.Goto Sheets(F + 1).Range("[COLOR=red]A1[/COLOR]")
  Exit Sub
Else
  For i = F + 1 To L - 1
    Set sht = Sheets(i)
    lastCol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column [COLOR=red]- 3
[/COLOR]    For colNo = 2 To lastCol Step 3
      sht.Cells(MonthOffset, colNo).FormulaR1C1 = "=SUMIF(Data!C2,R1C,Data!C3)"
    Next colNo
  Next i
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Something New or Something Old
Replies
3
Views
147
Fluff
Fluff
K
  • Solved
Dynamic Sum Range Addresses...(?)
Replies
2
Views
519
Kenny_D
K
T
  • Question Question
VBA to run when a table with a SQL query is refreshed?
Replies
3
Views
340
DickyWest32
D
OvernightCellebrity
  • Solved
SUMIF slowing calculation times
2
Replies
12
Views
477
kvsrinivasamurthy
K
S
  • Solved
Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2
Replies
5
Views
641
Alex Blakenburg
A

Forum statistics

Threads
1,225,171
Messages
6,183,327
Members
453,155
Latest member
joncaxddd

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