1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with excell macro - make code smaller

Discussion in 'Business Applications' started by spazem, Feb 5, 2013.

Thread Status:
Not open for further replies.
  1. spazem

    spazem Thread Starter

    Joined:
    Feb 5, 2013
    Messages:
    2
    Hi guys,

    i am no programmer. Our accountant wrote us an excell sheet to handle client money and now thats its full they want loads more cash just to make more sheets. We are a small business and cant afford these costs

    I have been able to work out how to add more sheets and spent some time adding from 25 to 50 sheets. This worked fine. Now we need more so i added up to 50 .

    The code works but now it says compile error. procedure to large.

    I am hoping that you guys can make the code smaller for me for it to work. here is a sample and the rest continues to P50. I am sure u gurus will understand it.

    Code:
    Sub Button2_Click()
    
    Dim NewRow As Integer
    Dim NewRow1 As Integer
    Dim NewRow2 As Integer
    Dim NewRow3 As Integer
    Dim NewRow4 As Integer
    Dim NewRow5 As Integer
    Dim NewRow6 As Integer
    Dim NewRow7 As Integer
    Dim NewRow8 As Integer
    Dim NewRow9 As Integer
    Dim NewRow10 As Integer
    Dim NewRow11 As Integer
    Dim NewRow12 As Integer
    Dim NewRow13 As Integer
    Dim NewRow14 As Integer
    Dim NewRow15 As Integer
    Dim NewRow16 As Integer
    Dim NewRow17 As Integer
    Dim NewRow18 As Integer
    Dim NewRow19 As Integer
    Dim NewRow20 As Integer
    Dim NewRow21 As Integer
    Dim NewRow22 As Integer
    Dim NewRow23 As Integer
    Dim NewRow24 As Integer
    Dim NewRow25 As Integer
    Dim NewRow26 As Integer
    Dim NewRow27 As Integer
    Dim NewRow28 As Integer
    Dim NewRow29 As Integer
    Dim NewRow30 As Integer
    Dim NewRow31 As Integer
    Dim NewRow32 As Integer
    Dim NewRow33 As Integer
    Dim NewRow34 As Integer
    Dim NewRow35 As Integer
    Dim NewRow36 As Integer
    Dim NewRow37 As Integer
    Dim NewRow38 As Integer
    Dim NewRow39 As Integer
    Dim NewRow40 As Integer
    Dim NewRow41 As Integer
    Dim NewRow42 As Integer
    Dim NewRow43 As Integer
    Dim NewRow44 As Integer
    Dim NewRow45 As Integer
    Dim NewRow46 As Integer
    Dim NewRow47 As Integer
    Dim NewRow48 As Integer
    Dim NewRow49 As Integer
    Dim NewRow50 As Integer
    Dim TransNo As Integer
    
    NewRow = Worksheets("input").Range("D12").Value + 1
    NewRow1 = Worksheets("input").Range("E12").Value + 1
    NewRow2 = Worksheets("input").Range("F12").Value + 1
    NewRow3 = Worksheets("input").Range("G12").Value + 1
    NewRow4 = Worksheets("input").Range("H12").Value + 1
    NewRow5 = Worksheets("input").Range("I12").Value + 1
    NewRow6 = Worksheets("input").Range("J12").Value + 1
    NewRow7 = Worksheets("input").Range("K12").Value + 1
    NewRow8 = Worksheets("input").Range("L12").Value + 1
    NewRow9 = Worksheets("input").Range("M12").Value + 1
    NewRow10 = Worksheets("input").Range("N12").Value + 1
    NewRow11 = Worksheets("input").Range("O12").Value + 1
    NewRow12 = Worksheets("input").Range("P12").Value + 1
    NewRow13 = Worksheets("input").Range("Q12").Value + 1
    NewRow14 = Worksheets("input").Range("R12").Value + 1
    NewRow15 = Worksheets("input").Range("S12").Value + 1
    NewRow16 = Worksheets("input").Range("T12").Value + 1
    NewRow17 = Worksheets("input").Range("U12").Value + 1
    NewRow18 = Worksheets("input").Range("V12").Value + 1
    NewRow19 = Worksheets("input").Range("W12").Value + 1
    NewRow20 = Worksheets("input").Range("X12").Value + 1
    NewRow21 = Worksheets("input").Range("Y12").Value + 1
    NewRow22 = Worksheets("input").Range("Z12").Value + 1
    NewRow23 = Worksheets("input").Range("AA12").Value + 1
    NewRow24 = Worksheets("input").Range("AB12").Value + 1
    NewRow25 = Worksheets("input").Range("AC12").Value + 1
    NewRow26 = Worksheets("input").Range("AD12").Value + 1
    NewRow27 = Worksheets("input").Range("AE12").Value + 1
    NewRow28 = Worksheets("input").Range("AF12").Value + 1
    NewRow29 = Worksheets("input").Range("AG12").Value + 1
    NewRow30 = Worksheets("input").Range("AH12").Value + 1
    NewRow31 = Worksheets("input").Range("AI12").Value + 1
    NewRow32 = Worksheets("input").Range("AJ12").Value + 1
    NewRow33 = Worksheets("input").Range("AK12").Value + 1
    NewRow34 = Worksheets("input").Range("AL12").Value + 1
    NewRow35 = Worksheets("input").Range("AM12").Value + 1
    NewRow36 = Worksheets("input").Range("AN12").Value + 1
    NewRow37 = Worksheets("input").Range("AO12").Value + 1
    NewRow38 = Worksheets("input").Range("AP12").Value + 1
    NewRow39 = Worksheets("input").Range("AQ12").Value + 1
    NewRow40 = Worksheets("input").Range("AR12").Value + 1
    NewRow41 = Worksheets("input").Range("AS12").Value + 1
    NewRow42 = Worksheets("input").Range("AT12").Value + 1
    NewRow43 = Worksheets("input").Range("AU12").Value + 1
    NewRow44 = Worksheets("input").Range("AV12").Value + 1
    NewRow45 = Worksheets("input").Range("AW12").Value + 1
    NewRow46 = Worksheets("input").Range("AX12").Value + 1
    NewRow47 = Worksheets("input").Range("AY12").Value + 1
    NewRow48 = Worksheets("input").Range("AZ12").Value + 1
    NewRow49 = Worksheets("input").Range("BA12").Value + 1
    NewRow50 = Worksheets("input").Range("BB12").Value + 1
    TransNo = Worksheets("input").Range("D13").Value + 1
    
    Worksheets("cashbook").Cells(NewRow, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("cashbook").Cells(NewRow, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("cashbook").Cells(NewRow, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("cashbook").Cells(NewRow, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("cashbook").Cells(NewRow, 6).Value = Worksheets("input").Range("B9").Value
    Worksheets("cashbook").Cells(NewRow, 7).Value = Worksheets("input").Range("B10").Value
    Worksheets("cashbook").Cells(NewRow, 8).Value = Worksheets("cashbook").Cells(NewRow - 1, 8).Value + Worksheets("cashbook").Cells(NewRow, 6).Value - Worksheets("cashbook").Cells(NewRow, 7).Value
    Worksheets("cashbook").Cells(NewRow, 1).Value = Worksheets("input").Range("D13").Value
    
    If Worksheets("input").Range("B6").Value = 1 Then
    
    
    Worksheets("P1").Cells(NewRow1, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P1").Cells(NewRow1, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P1").Cells(NewRow1, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P1").Cells(NewRow1, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P1").Cells(NewRow1, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P1").Cells(NewRow1, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P1").Cells(NewRow1, 8).Value = Worksheets("P1").Cells(NewRow1 - 1, 8).Value + Worksheets("P1").Cells(NewRow1, 6).Value - Worksheets("P1").Cells(NewRow1, 7).Value
    Worksheets("P1").Cells(NewRow1, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("E12").Value = NewRow1
    
    End If
    
    If Worksheets("input").Range("B6").Value = 2 Then
    
    
    Worksheets("P2").Cells(NewRow2, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P2").Cells(NewRow2, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P2").Cells(NewRow2, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P2").Cells(NewRow2, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P2").Cells(NewRow2, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P2").Cells(NewRow2, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P2").Cells(NewRow2, 8).Value = Worksheets("P2").Cells(NewRow2 - 1, 8).Value + Worksheets("P2").Cells(NewRow2, 6).Value - Worksheets("P2").Cells(NewRow2, 7).Value
    Worksheets("P2").Cells(NewRow2, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("F12").Value = NewRow2
    
    End If
    
    If Worksheets("input").Range("B6").Value = 3 Then
    
    
    Worksheets("P3").Cells(NewRow3, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P3").Cells(NewRow3, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P3").Cells(NewRow3, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P3").Cells(NewRow3, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P3").Cells(NewRow3, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P3").Cells(NewRow3, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P3").Cells(NewRow3, 8).Value = Worksheets("P3").Cells(NewRow3 - 1, 8).Value + Worksheets("P3").Cells(NewRow3, 6).Value - Worksheets("P3").Cells(NewRow3, 7).Value
    Worksheets("P3").Cells(NewRow3, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("G12").Value = NewRow3
    
    End If
    
    If Worksheets("input").Range("B6").Value = 4 Then
    
    
    Worksheets("P4").Cells(NewRow4, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P4").Cells(NewRow4, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P4").Cells(NewRow4, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P4").Cells(NewRow4, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P4").Cells(NewRow4, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P4").Cells(NewRow4, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P4").Cells(NewRow4, 8).Value = Worksheets("P4").Cells(NewRow4 - 1, 8).Value + Worksheets("P4").Cells(NewRow4, 6).Value - Worksheets("P4").Cells(NewRow4, 7).Value
    Worksheets("P4").Cells(NewRow4, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("H12").Value = NewRow4
    
    End If
    
    If Worksheets("input").Range("B6").Value = 5 Then
    
    
    Worksheets("P5").Cells(NewRow5, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P5").Cells(NewRow5, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P5").Cells(NewRow5, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P5").Cells(NewRow5, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P5").Cells(NewRow5, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P5").Cells(NewRow5, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P5").Cells(NewRow5, 8).Value = Worksheets("P5").Cells(NewRow5 - 1, 8).Value + Worksheets("P5").Cells(NewRow5, 6).Value - Worksheets("P5").Cells(NewRow5, 7).Value
    Worksheets("P5").Cells(NewRow5, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("I12").Value = NewRow5
    
    End If
    
    If Worksheets("input").Range("B6").Value = 6 Then
    
    
    Worksheets("P6").Cells(NewRow6, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P6").Cells(NewRow6, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P6").Cells(NewRow6, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P6").Cells(NewRow6, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P6").Cells(NewRow6, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P6").Cells(NewRow6, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P6").Cells(NewRow6, 8).Value = Worksheets("P6").Cells(NewRow6 - 1, 8).Value + Worksheets("P6").Cells(NewRow6, 6).Value - Worksheets("P6").Cells(NewRow6, 7).Value
    Worksheets("P6").Cells(NewRow6, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("J12").Value = NewRow6
    
    End If
    
    If Worksheets("input").Range("B6").Value = 7 Then
    
    Worksheets("P7").Cells(NewRow7, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P7").Cells(NewRow7, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P7").Cells(NewRow7, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P7").Cells(NewRow7, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P7").Cells(NewRow7, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P7").Cells(NewRow7, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P7").Cells(NewRow7, 8).Value = Worksheets("P7").Cells(NewRow7 - 1, 8).Value + Worksheets("P7").Cells(NewRow7, 6).Value - Worksheets("P7").Cells(NewRow7, 7).Value
    Worksheets("P7").Cells(NewRow7, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("K12").Value = NewRow7
    
    End If
    
    If Worksheets("input").Range("B6").Value = 8 Then
    
    
    Worksheets("P8").Cells(NewRow8, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P8").Cells(NewRow8, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P8").Cells(NewRow8, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P8").Cells(NewRow8, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P8").Cells(NewRow8, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P8").Cells(NewRow8, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P8").Cells(NewRow8, 8).Value = Worksheets("P8").Cells(NewRow8 - 1, 8).Value + Worksheets("P8").Cells(NewRow8, 6).Value - Worksheets("P8").Cells(NewRow8, 7).Value
    Worksheets("P8").Cells(NewRow8, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("L12").Value = NewRow8
    
    End If
    
    If Worksheets("input").Range("B6").Value = 9 Then
    
    
    Worksheets("P9").Cells(NewRow9, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P9").Cells(NewRow9, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P9").Cells(NewRow9, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P9").Cells(NewRow9, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P9").Cells(NewRow9, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P9").Cells(NewRow9, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P9").Cells(NewRow9, 8).Value = Worksheets("P9").Cells(NewRow9 - 1, 8).Value + Worksheets("P9").Cells(NewRow9, 6).Value - Worksheets("P9").Cells(NewRow9, 7).Value
    Worksheets("P9").Cells(NewRow9, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("M12").Value = NewRow9
    
    End If
    
    If Worksheets("input").Range("B6").Value = 10 Then
    
    
    Worksheets("P10").Cells(NewRow10, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P10").Cells(NewRow10, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P10").Cells(NewRow10, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P10").Cells(NewRow10, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P10").Cells(NewRow10, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P10").Cells(NewRow10, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P10").Cells(NewRow10, 8).Value = Worksheets("P10").Cells(NewRow10 - 1, 8).Value + Worksheets("P10").Cells(NewRow10, 6).Value - Worksheets("P10").Cells(NewRow10, 7).Value
    Worksheets("P10").Cells(NewRow10, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("N12").Value = NewRow10
    
    End If
    
    If Worksheets("input").Range("B6").Value = 11 Then
    
    Worksheets("P11").Cells(NewRow11, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P11").Cells(NewRow11, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P11").Cells(NewRow11, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P11").Cells(NewRow11, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P11").Cells(NewRow11, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P11").Cells(NewRow11, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P11").Cells(NewRow11, 8).Value = Worksheets("P11").Cells(NewRow11 - 1, 8).Value + Worksheets("P11").Cells(NewRow11, 6).Value - Worksheets("P11").Cells(NewRow11, 7).Value
    Worksheets("P11").Cells(NewRow11, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("O12").Value = NewRow11
    
    End If
    
    If Worksheets("input").Range("B6").Value = 12 Then
    
    
    Worksheets("P12").Cells(NewRow12, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P12").Cells(NewRow12, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P12").Cells(NewRow12, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P12").Cells(NewRow12, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P12").Cells(NewRow12, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P12").Cells(NewRow12, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P12").Cells(NewRow12, 8).Value = Worksheets("P12").Cells(NewRow12 - 1, 8).Value + Worksheets("P12").Cells(NewRow12, 6).Value - Worksheets("P12").Cells(NewRow12, 7).Value
    Worksheets("P12").Cells(NewRow12, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("P12").Value = NewRow12
    
    End If
    
    If Worksheets("input").Range("B6").Value = 13 Then
    
    Worksheets("P13").Cells(NewRow13, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P13").Cells(NewRow13, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P13").Cells(NewRow13, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P13").Cells(NewRow13, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P13").Cells(NewRow13, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P13").Cells(NewRow13, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P13").Cells(NewRow13, 8).Value = Worksheets("P13").Cells(NewRow13 - 1, 8).Value + Worksheets("P13").Cells(NewRow13, 6).Value - Worksheets("P13").Cells(NewRow13, 7).Value
    Worksheets("P13").Cells(NewRow13, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("Q12").Value = NewRow13
    
    End If
    
    If Worksheets("input").Range("B6").Value = 14 Then
    
    Worksheets("P14").Cells(NewRow14, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P14").Cells(NewRow14, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P14").Cells(NewRow14, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P14").Cells(NewRow14, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P14").Cells(NewRow14, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P14").Cells(NewRow14, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P14").Cells(NewRow14, 8).Value = Worksheets("P14").Cells(NewRow14 - 1, 8).Value + Worksheets("P14").Cells(NewRow14, 6).Value - Worksheets("P14").Cells(NewRow14, 7).Value
    Worksheets("P14").Cells(NewRow14, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("R12").Value = NewRow14
    
    End If
    
    If Worksheets("input").Range("B6").Value = 15 Then
    
    Worksheets("P15").Cells(NewRow15, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P15").Cells(NewRow15, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P15").Cells(NewRow15, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P15").Cells(NewRow15, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P15").Cells(NewRow15, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P15").Cells(NewRow15, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P15").Cells(NewRow15, 8).Value = Worksheets("P15").Cells(NewRow15 - 1, 8).Value + Worksheets("P15").Cells(NewRow15, 6).Value - Worksheets("P15").Cells(NewRow15, 7).Value
    Worksheets("P15").Cells(NewRow15, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("S12").Value = NewRow15
    
    End If
    
    If Worksheets("input").Range("B6").Value = 16 Then
    
    Worksheets("P16").Cells(NewRow16, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P16").Cells(NewRow16, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P16").Cells(NewRow16, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P16").Cells(NewRow16, 5).Value = Worksheets("input").Range("B8").Value
    Worksheets("P16").Cells(NewRow16, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P16").Cells(NewRow16, 6).Value = Worksheets("input").Range("B10").Value
    Worksheets("P16").Cells(NewRow16, 8).Value = Worksheets("P16").Cells(NewRow16 - 1, 8).Value + Worksheets("P16").Cells(NewRow16, 6).Value - Worksheets("P16").Cells(NewRow16, 7).Value
    Worksheets("P16").Cells(NewRow16, 1).Value = Worksheets("input").Range("D13").Value
    
    Worksheets("input").Range("T12").Value = NewRow16
    
    End If
    
    
    This continues to p50 with this at the end

    Code:
    MsgBox "New Data added", vbOKOnly, "Test"
    
    Worksheets("input").Range("B5").ClearContents
    Worksheets("input").Range("B6").ClearContents
    Worksheets("input").Range("B7").ClearContents
    Worksheets("input").Range("B8").ClearContents
    Worksheets("input").Range("B9").ClearContents
    Worksheets("input").Range("B10").ClearContents
    
    Worksheets("input").Range("D12").Value = NewRow
    Worksheets("input").Range("D13").Value = TransNo
    
    Worksheets("input").Range("B5").Select
    i know the code works as i added 5 more sheets and it worked. Please help me simplify it to i can get more out of it.

    Thanks for your help and reading this.

    jason
     
  2. spazem

    spazem Thread Starter

    Joined:
    Feb 5, 2013
    Messages:
    2
    any1?
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi. :)

    You have a critical problem with lines starting (example):

    Worksheets("P1").Cells(NewRow1, 8).Value = Worksheets("P1").Cells(NewRow1 - 1, 8).Value

    . When NewRow1 = 1, NewRow1 - 1 will give you Cells(0,8). So that won't fly.

    You have multiple "If Worksheets("input").Range("B6").Value = n Then" sections. You can replace these with one section. First assign Input!B6 to a variable -- "P_Num", for example.

    Then use that to create the name of the sheet to be modified, as follows:

    P_Num = Worksheets("input").Range("B6").Value

    Worksheets("P" & P_Num).Cells(NewRow1, 2).Value = Worksheets("input").Range("B5").Value
    Worksheets("P" & P_Num).Cells(NewRow1, 3).Value = Worksheets("input").Range("B6").Value
    Worksheets("P" & P_Num).Cells(NewRow1, 4).Value = Worksheets("input").Range("B7").Value
    Worksheets("P" & P_Num).Cells(NewRow1, 5).Value = Worksheets("input").Range("B8").Value

    Worksheets("P" & P_Num).Cells(NewRow1, 7).Value = Worksheets("input").Range("B9").Value
    Worksheets("P" & P_Num).Cells(NewRow1, 6).Value = Worksheets("input").Range("B10").Value
    'missing line due to NewRow1-1 baulks at NewRow1 = 1 issue
    Worksheets("P" & P_Num).Cells(NewRow1, 7).Value
    Worksheets("P" & P_Num).Cells(NewRow1, 1).Value = Worksheets("input").Range("D13").Value

    Worksheets("input").Range("D12").Offset(0, P_Num).Value = NewRow1


    (the 4 bold lines can actually be cut to one line with Application.Transpose but we'll see if you follow this first)

    Any help?
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088286

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice