VBA copy paste code... Where did I write it wrong?

[dukeofscouts]

Over the past several posts I've tried looking at the diffrent issues I was having trying to create my vision of macro that would allow me to transfer data from one sheet to another and place it correctly in the second sheet using a copy paste tool.

Well I thought I had it all in place. I gave up on looking at the range b2:b8 to see if the class meet on any days sunday to saturday. Instead I'm justing using a series of MsgBox to perform a similar task.

As best I can tell the code works up to the point that it goes to paste, the macro then stops and gives me an error message. does any one who actually knows VBA see my error.

Sub classentry()
If MsgBox("Does the class meet Sunday?", vbYesNo) = vbYes Then
Sheets("blank sheet (2)").Select
Range("b1").Select
ActiveCell.Offset(Range("'class entry'!f5"), 0).Select
Sheets("Class Entry").Select
Range("b4").Select
Range("b4:b" & Range("f4").Value).Select
Selection.Copy
Sheets("blank sheet (2)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks_:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipblanks_:=False, Tanspose:=False
Sheets("Class Entry").Select
Range("a1").Select
Application.CutCopyMode = False
End If
If MsgBox("Does the class meet Monday?", vbYesNo) = vbYes Then
Sheets("blank sheet (2)").Select
Range("C1").Select
ActiveCell.Offset(Range("'class entry'!f5"), 0).Select
Sheets("Class Entry").Select
Range("b4").Select
Range("b4:b" & Range("f4").Value).Select
Selection.Copy
Sheets("blank sheet (2)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks_:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipblanks_:=False, Tanspose:=False
Sheets("Class Entry").Select
Range("a1").Select
Application.CutCopyMode = False
End If
If MsgBox("Does the class meet Tuesday?", vbYesNo) = vbYes Then
Sheets("blank sheet (2)").Select
Range("d1").Select
ActiveCell.Offset(Range("'class entry'!f5"), 0).Select
Sheets("Class Entry").Select
Range("b4").Select
Range("b4:b" & Range("f4").Value).Select
Selection.Copy
Sheets("blank sheet (2)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks_:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipblanks_:=False, Tanspose:=False
Sheets("Class Entry").Select
Range("a1").Select
Application.CutCopyMode = False
End If
If MsgBox("Does the class meet Wedneday?", vbYesNo) = vbYes Then
Sheets("blank sheet (2)").Select
Range("e1").Select
ActiveCell.Offset(Range("'class entry'!f5"), 0).Select
Sheets("Class Entry").Select
Range("b4").Select
Range("b4:b" & Range("f4").Value).Select
Selection.Copy
Sheets("blank sheet (2)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks_:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipblanks_:=False, Tanspose:=False
Sheets("Class Entry").Select
Range("a1").Select
Application.CutCopyMode = False
End If
If MsgBox("Does the class meet Thursday?", vbYesNo) = vbYes Then
Sheets("blank sheet (2)").Select
Range("f1").Select
ActiveCell.Offset(Range("'class entry'!f5"), 0).Select
Sheets("Class Entry").Select
Range("b4").Select
Range("b4:b" & Range("f4").Value).Select
Selection.Copy
Sheets("blank sheet (2)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks_:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipblanks_:=False, Tanspose:=False
Sheets("Class Entry").Select
Range("a1").Select
Application.CutCopyMode = False
End If
If MsgBox("Does the class meet Friday?", vbYesNo) = vbYes Then
Sheets("blank sheet (2)").Select
Range("g1").Select
ActiveCell.Offset(Range("'class entry'!f5"), 0).Select
Sheets("Class Entry").Select
Range("b4").Select
Range("b4:b" & Range("f4").Value).Select
Selection.Copy
Sheets("blank sheet (2)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks_:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipblanks_:=False, Tanspose:=False
Sheets("Class Entry").Select
Range("a1").Select
Application.CutCopyMode = False
End If
If MsgBox("Does the class meet Saturday?", vbYesNo) = vbYes Then
Sheets("blank sheet (2)").Select
Range("h1").Select
ActiveCell.Offset(Range("'class entry'!f5"), 0).Select
Sheets("Class Entry").Select
Range("b4").Select
Range("b4:b" & Range("f4").Value).Select
Selection.Copy
Sheets("blank sheet (2)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks_:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipblanks_:=False, Tanspose:=False
Sheets("Class Entry").Select
Range("a1").Select
Application.CutCopyMode = False
End If
End Sub

Lost and confused,
D.O.S.

 

[dukeofscouts]

still have then and else problem but here is the updated code:

Sub Macro4()
'
' Macro4 Macro
'
'
Sheets("blank sheet (2)").Select
If MsgBox("does class meet Sunday?", vbYesNo) = ybno Then
MsgBox "no class on this day", vbOKOnly
Else
Range("B1").Select
ActiveCell.Offset(Range("'Class Entry'!f5").Value, 0).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(ADDRESS(4,4,4,1,""Class entry""))"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(ADDRESS(5,4,4,1,""Class entry""))"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(ADDRESS(6,4,4,1,""Class entry""))"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(ADDRESS(7,4,4,1,""Class entry""))"
ActiveCell.Offset(-3, 0).Select
Sheets("Class entry").Select
Range("D4").Select
ActiveCell.Range("A1:A4").Select
Selection.Copy
Sheets("blank sheet (2)").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Range("A1:A4").Copy
ActiveCell.Range("A1:A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
If MsgBox("Clear Class info?", ybyesno) = ybno Then Exit Sub
Sheets("Class entry").Select
ActiveCell.Offset(0, -2).Range("A1:A9").Select
Selection.ClearContents
ActiveCell.Offset(-2, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FormulaR1C1 = "no"
ActiveCell.Offset(10, 0).Range("A1").Select
Sheets("blank sheet (2)").Select
End Sub

 

[dukeofscouts]

Does any one have a suggestion for how to add the "*" to the cells that would be covered by each class if the class is over an hour? I thought of using "activecell.range(Range"....").value,0).select" but this would not work if the class was an hour long.

any suggestions?

 

[dukeofscouts]

I'm trying to write this code yet again. I'm going to call this string done and move into a fresh one that perhaps will have better results and less confusion