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]

Well looks like I'm going to be trying this all over again. I've got some new issues, but still dealing with some of the old issues too. To make things simple I've cut the code down so it is only doing one day. Once I get the code working it will be a simple matter of doing a copy and paste then editing the code each time to reflect the day that I may or may not need to enter the class under.

If any one knows a code that will allow me to get around the Msgbox idea I'm all for it, but that is probobly a long time in the works.

The main issue right now is that when I run the macro it gets to the pastespecial and gives me error "Run time error: 1004 application or object name error" I think it is also having a hard time getting the correct range of cells to copy and then to paste, but I'll be able to better see this once the paste starts going.

Would it help if I had a version of this on office live for you to see and try to work with?

 

[snowblizz]

Posting the data with Excel Jeanie might help.
What you have and how you'd like it to look when working.
Because then we are also looking on what you are actually trying to do.

I remember I looked at it yesterday and got the same error as you on the pastespecial operation. Unfortunatley the error messages in excel are nto exactly too enlightening, to me atleast.

Are you entirely sure you need to use the pastespecial version. As rugila posted an example of a simpler (and working!) copy operation.

 

[dukeofscouts]

The way I have the forumals and condtional formatting set I need to paste the values and all the formatting, not just the number formatting. Otherwise you'd both be right that would be a better idea.

 

[snowblizz]

The way I have the forumals and condtional formatting set I need to paste the values and all the formatting, not just the number formatting. Otherwise you'd both be right that would be a better idea.

I'm testing a bit and am now getting errors all over the code. Basically what I think is happening is that all the "selecting" is interfering with the copy and paste operations.

What I meant was that you can apply the formatting after the copy using VBA code.

You are correct in that you might want to start from scratch, at least trying to articulate from which cells you want to make copies.

I suggest you try and "hardcore" the cell references first in VBA and then we can help with making them dynamic.

What you want to do CAN be done, I'm fairly sure of it. And without Msgboxes, we just need to keep at it really.

 

[dukeofscouts]

Here is a photo story board that shows what I'd like to have happen. I'm going to look into downloading one of the screen recorders so I can record a video of the goal so we can have a better idea of what is going on.

http://www.facebook.com/album.php?aid=2061067&id=215906087&l=ca4fb6301a

 

[snowblizz]

I've been following several copy/paste problem threads and late last night got I some good explanations to what could be falling apart. Explanation here: http://www.mrexcel.com/forum/showthread.php?t=393925

Made some tests on the following code snippet.

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

And came to the following conclusions. You can't have two Pastespecials after each other, at least not in this case. The following replaces the 2 pastes with one.

 Sheets("Sheet (3)").[F5].PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, skipblanks:=False, Transpose:=False

Also note the extra "_" in "skipblanks" that you had in the code. And that

Operation:=xlNone, skipblanks:=False, Transpose:=False

are probably not needed at all.
You need to give the Pastespecial operation a target. I.e. like above it paste to "F5". I used a shorthand but if you want to make it dynamic replace [F5] with Range("F5") instead.

 

[dukeofscouts]

Ok here is a recording of what I'm hoping the macro will do. This the most efficent way I could think of to show the formulas, their arrangment, and the basic workbook set-up. Hope this Helps:
http://www.youtube.com/watch?v=-3Zwagp2CYM

Yours in excel,
D.O.S.

 

[dukeofscouts]

Ok so I tried to do a new approach. I used the recorder and then edited the code it ran and came up with a code that looks like this:
Sub Macro4()
'
' Macro4 Macro
'
'
Sheets("blank sheet (2)").Select
If MsgBox("does class meet Sunday?", vbYesNo) = ybyes Then
Range("Table18[[#Headers],[Sunday]]").Select
ActiveCell.Offset(Range("'Class Entry'!f5").Value, 0).Range("Table18[[#Headers],[Time]]").Select
ActiveCell.FormulaR1C1 = "='Class entry'!R[-25]C[2]"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A4"), Type:= _
xlFillValues
ActiveCell.Range("A1:A4").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
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
Else
MsgBox "no class on this day", vbOKOnly
End If
' Repeat for each weekday. Change msgbox and header absolute referance
End Sub

The problem I have now is that it runs the else code, even if I click yes. How do I get it to run the then code?

P.S. did the youtube help at all?

 

[Legacy 14611]

If MsgBox("does class meet Sunday?", vbYesNo) = vbyes Then

 

[dukeofscouts]

tried that too. It still skips the then code and goes to the else code. Only now the else code runs what was in the then and the then what was in the else.

if... vbyes = then
1
else
2
end if
'or
if... =vbno then
2
else
1
'both only run else code