I have this code but am not sure how to make it work for multiple cells
in the same column. I just want it to look down column X and perform
one operation if there is a 1 and another if there is a 2. I have it
set up to look in one cell copying this multiple times and changing the
cell refrences dosent seem practical espically with 100+ cells. Thanks
for the help.
My code:
Dim vS As Variant
vS = Sheets(3).Range("X5")
If Not IsNumeric(vS) Then
Exit Sub
Else
Select Case vS
Case 1
If MsgBox("Are you sure you want to delete?", _
vbYesNo, "ExcelTips") = vbYes Then
Application.DisplayAlerts = False
Sheets("Approved").Select
Range("B5:F5").Select
Selection.Copy
Sheets("Del 2006").Select
If (Range("B207")) = "" Then
Range("B207").Select
ActiveSheet.Paste
Else
Range("B4").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End If
Sheets("Approved").Select
Range("J5:V5").Select
Selection.Copy
Sheets("Del 2006").Select

If (Range("J207")) = "" Then
Range("J207").Select
ActiveSheet.Paste
Else
Sheets("Del 2006").Select
Range("J4").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End If
Range("B206").Select
Selection.Copy
Range("B207:B500").Select
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Adding Date
Range("H207").Select
If (Range("H207")) = "" Then
ActiveCell.Value = Date
Else
Range("H4").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = Date
NumberFormat = "mm/dd/yyyy"
End If
Application.DisplayAlerts = True
Sheets("Approved").Select
Range("X5").Select
Selection.Delete
End If
'Adding a Credit
Case 2
If MsgBox("Is this a new addition?", _
vbYesNo, "ExcelTips") = vbYes Then
Application.DisplayAlerts = False
Sheets("Approved").Select
Range("B5:F5").Select
Selection.Copy
Sheets("Add 2006").Select
If (Range("B281")) = "" Then
Range("B281").Select
ActiveSheet.Paste
Else
Range("B280").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End If
Sheets("Approved").Select
Range("J5:V5").Select
Selection.Copy
Sheets("Add 2006").Select
If (Range("J281")) = "" Then
Range("J281").Select
ActiveSheet.Paste
Else
Sheets("Add 2006").Select
Range("J279").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End If
Range("B278").Select
Selection.Copy
Range("B281:B500").Select
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Adding Date
Range("H281").Select
If (Range("H281")) = "" Then
ActiveCell.Value = Date
Else
Range("H280").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = Date
NumberFormat = "mm/dd/yyyy"
End If
Application.DisplayAlerts = True
Sheets("Approved").Select
Range("X5").Select
Selection.Delete
End If
End Select
End If