VNouBA
05-17-2012, 08:32 AM
Shortening VB Code
Basically I need the following code to be shorten but to add itself to the next available row.
If Target.Address = "$B$5" Then
If Sheets("Report").Range("J5") = "" Then
Sheets("Report").Range("J5") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "J").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$D$5" Then
If Sheets("Report").Range("L5") = "" Then
Sheets("Report").Range("L5") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "L").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$H$5" Then
If Sheets("Report").Range("M5") = "" Then
Sheets("Report").Range("M5") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "M").End(xlUp)(2).Value = Target.Value
End If
End If
If (Target.Address = "$I$5" Or Target.Address = "$J$5") And Target.Value <> "" Then
Range("I5,J5").Copy
If Sheets("Report").Range("N5").Value = "" Then
Sheets("Report").Range("N5").PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
If Target.Address = "$B$6" Then
If Sheets("Report").Range("J6") = "" Then
Sheets("Report").Range("J6") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "J").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$D$6" Then
If Sheets("Report").Range("L6") = "" Then
Sheets("Report").Range("L6") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "L").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$H$6" Then
If Sheets("Report").Range("M6") = "" Then
Sheets("Report").Range("M6") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "M").End(xlUp)(2).Value = Target.Value
End If
End If
If (Target.Address = "$I$6" Or Target.Address = "$J$6") And Target.Value <> "" Then
Range("I6,J6").Copy
If Sheets("Report").Range("N6").Value = "" Then
Sheets("Report").Range("N6").PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
If Target.Address = "$B$7" Then
If Sheets("Report").Range("J7") = "" Then
Sheets("Report").Range("J7") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "J").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$D$7" Then
If Sheets("Report").Range("L7") = "" Then
Sheets("Report").Range("L7") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "L").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$H$7" Then
If Sheets("Report").Range("M7") = "" Then
Sheets("Report").Range("M7") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "M").End(xlUp)(2).Value = Target.Value
End If
End If
If (Target.Address = "$I$7" Or Target.Address = "$J$7") And Target.Value <> "" Then
Range("I7,J7").Copy
If Sheets("Report").Range("N7").Value = "" Then
Sheets("Report").Range("N7").PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
Does someone know a quick trick?
I am doing copy and paste right now on this code all the way to row 30 but might have to do more (until 100) this could take days if I modify any columns.
Thank you in advance
Basically I need the following code to be shorten but to add itself to the next available row.
If Target.Address = "$B$5" Then
If Sheets("Report").Range("J5") = "" Then
Sheets("Report").Range("J5") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "J").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$D$5" Then
If Sheets("Report").Range("L5") = "" Then
Sheets("Report").Range("L5") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "L").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$H$5" Then
If Sheets("Report").Range("M5") = "" Then
Sheets("Report").Range("M5") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "M").End(xlUp)(2).Value = Target.Value
End If
End If
If (Target.Address = "$I$5" Or Target.Address = "$J$5") And Target.Value <> "" Then
Range("I5,J5").Copy
If Sheets("Report").Range("N5").Value = "" Then
Sheets("Report").Range("N5").PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
If Target.Address = "$B$6" Then
If Sheets("Report").Range("J6") = "" Then
Sheets("Report").Range("J6") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "J").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$D$6" Then
If Sheets("Report").Range("L6") = "" Then
Sheets("Report").Range("L6") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "L").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$H$6" Then
If Sheets("Report").Range("M6") = "" Then
Sheets("Report").Range("M6") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "M").End(xlUp)(2).Value = Target.Value
End If
End If
If (Target.Address = "$I$6" Or Target.Address = "$J$6") And Target.Value <> "" Then
Range("I6,J6").Copy
If Sheets("Report").Range("N6").Value = "" Then
Sheets("Report").Range("N6").PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
If Target.Address = "$B$7" Then
If Sheets("Report").Range("J7") = "" Then
Sheets("Report").Range("J7") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "J").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$D$7" Then
If Sheets("Report").Range("L7") = "" Then
Sheets("Report").Range("L7") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "L").End(xlUp)(2).Value = Target.Value
End If
End If
If Target.Address = "$H$7" Then
If Sheets("Report").Range("M7") = "" Then
Sheets("Report").Range("M7") = Target.Value
Else
Sheets("Report").Cells(Rows.Count, "M").End(xlUp)(2).Value = Target.Value
End If
End If
If (Target.Address = "$I$7" Or Target.Address = "$J$7") And Target.Value <> "" Then
Range("I7,J7").Copy
If Sheets("Report").Range("N7").Value = "" Then
Sheets("Report").Range("N7").PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
Does someone know a quick trick?
I am doing copy and paste right now on this code all the way to row 30 but might have to do more (until 100) this could take days if I modify any columns.
Thank you in advance