Hi my excel book keeps loosing its macros, they just vanish, im wondering if i make the code smaller if it could help.
Could anyone make this macro smaller ?
Thank
Could anyone make this macro smaller ?
Thank
VBA Code:
Sub COPY_INTERESTS()
Dim i As Long
For i = 1 To 101
With Sheets(CStr(i))
If LCase(.Range("yo4").Value) = "yes" Then
.Range("E4:AA7").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo8").Value) = "yes" Then
.Range("E8:AA11").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo12").Value) = "yes" Then
.Range("E12:AA15").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo16").Value) = "yes" Then
.Range("E16:AA19").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo20").Value) = "yes" Then
.Range("E20:AA23").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo24").Value) = "yes" Then
.Range("E24:AA27").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo28").Value) = "yes" Then
.Range("E28:AA31").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo32").Value) = "yes" Then
.Range("E32:AA35").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo36").Value) = "yes" Then
.Range("E36:AA39").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo40").Value) = "yes" Then
.Range("E40:AA43").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo44").Value) = "yes" Then
.Range("E44:AA47").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo48").Value) = "yes" Then
.Range("E48:AA51").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo52").Value) = "yes" Then
.Range("E52:AA55").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo56").Value) = "yes" Then
.Range("E56:AA59").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo60").Value) = "yes" Then
.Range("A60:AA63").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo64").Value) = "yes" Then
.Range("E64:AA67").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo68").Value) = "yes" Then
.Range("E68:AA71").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo72").Value) = "yes" Then
.Range("E72:AA75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo4").Value) = "yesl" Then
.Range("E4:AA7").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo8").Value) = "yesl" Then
.Range("E8:AA11").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo12").Value) = "yesl" Then
.Range("E12:AA15").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo16").Value) = "yesl" Then
.Range("E16:AA19").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo20").Value) = "yesl" Then
.Range("E20:AA23").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo24").Value) = "yesl" Then
.Range("E24:AA27").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo28").Value) = "yesl" Then
.Range("E28:AA31").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo32").Value) = "yesl" Then
.Range("E32:AA35").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo36").Value) = "yesl" Then
.Range("E36:AA39").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo40").Value) = "yesl" Then
.Range("E40:AA43").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo44").Value) = "yesl" Then
.Range("E44:AA47").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo48").Value) = "yesl" Then
.Range("E48:AA51").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo52").Value) = "yesl" Then
.Range("E52:AA55").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo56").Value) = "yesl" Then
.Range("E56:AA59").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo60").Value) = "yesl" Then
.Range("A60:AA63").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo64").Value) = "yesl" Then
.Range("E64:AA67").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo68").Value) = "yesl" Then
.Range("E68:AA71").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("yo72").Value) = "yesl" Then
.Range("E72:AA75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("b1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
If LCase(.Range("vZ2").Value) = "yes" Then
.Range("E4:E75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AA" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("Yu4:Yu75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AB" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("yy4:yz75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AC" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("zb4:zb75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AE" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("ZC4:ZC75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AF" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("ZG4:ZH75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AG" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("ZJ4:ZJ75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AI" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("ZT4:ZT75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AJ" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("ZX4:ZY75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AK" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("AAA4:AAA75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AM" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("AAB4:AAJ75").Copy
Workbooks("book1").Sheets("INTERESTS").Range("AN" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
End With
Next i
End Sub