Help with excell macro - make code smaller

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top