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.

 

[Legacy 14611]

It looks an excessively prolix macro for what appears a straightforward problem.

If you state in words or by simple example just what you want to achieve then it's likely that straightforward means of achieving your aims would be forthcoming.

 

[dukeofscouts]

Well basicaly I have a workbook that I started using back in 2005 to show my class load on a weekly basis, visually. I used this workbook to help me select what class I could take. I set it up with the days of the week at the top and time of day (in quarter hours) running down the side. Everything worked fine putting the data in manually for the past three and half years. However, now that I know a little VBA I wanted to push myself to try and get a macro that could take me data and put the classes into the sheet for me, so I couldn't skrew it up.

Thus I made a form in a new sheet 'class entry' that uses the concatinate formula to combine all the needed details of each class and then an if formula to make sure the class was the correct height to represent the class.

My next task was this VBA code. I wanted the code to be able to cope the correct cells from my range of forumals, check the days the class meet from the cells in b2:h2, and then paste into the 'blank sheet (2)' sheet the class info on the correct days and at the correct times.

After playing around with some tricks I saw on the www.youtube.com/user/excelisfun site I came up with using the range("[cell address]") trick to move the target range of cells, and with a little help from previos posts in the message board figured out how to adjust the number of cell I selected to copy. This only left trying to figure out how to have the macro select the right days. I wasn't able to figure out on my own how to get the macro to look at the range of trues and falses in B2:h2. So, I compromised and decided to use the msgbox tool and get a similar result.

Does that help any?

 

[Legacy 14611]

Is your situation then that your code does everything you want except check the number of and/or do something else with the Trues and Falses in the range B2:H2?

You can shorten your code considerably (and shorter code usually means errors are easier to track down) by not selecting cells/ranges before you do anything with them. e.g. in the first few lines you have

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

Could you make this

Sheets("Class Entry").Range("b4:b" & Range("f4").Value).copy Sheets("blank sheet (2)")

etc. (although even then it's not clear to me just where on Sheets("blank sheet (2)") you want your range to be copied to.)

 

[dukeofscouts]

You're close. See in Row 1 I've got Sunday, Monday, Tuesday... then in Row 2 thanks to some Data Validation I can select True or False for each day. My thought was to have the macro read this range and then move to 'blank sheet (2)' and get the correct days of the week that way. As for shortening the code I'm all for it provided the code will still be read by the computer. That will be tomorrow's project so I'll let you know if I have any progress with it. My main problem right now is that there is some issue that the computer sees in the pastespecial code that makes it want to kick it back to me with an error message.

Does that clear it up at all?
D.O.S.

 

[Legacy 14611]

As far as I can tell, you want a line or lines in your code to do this:

Sheets("Class Entry").Range("b4:f4").copy Sheets("blank sheet (2)").range("b4")

 

[snowblizz]

It would be good to post the error message (and number) you get.

But here's one thing that I usually run into. When copying and pasting if you select cells then Excel will complain if the selection you try and paste to is not of the same size as the original range. Since you make selections that MIGHT be why it hangs up on the pastes.

Either way working away the ".Select" and "Selection." would most likely benefit you.

Also the two Paste lines could probably be replace with one and the following property "xlPasteValuesAndNumberFormats".

 

[dukeofscouts]

Has to be the same size... good to know that would be the problem right there.

Thanks,
D.O.S.

 

[dukeofscouts]

New error: 104: _Global
Looks like it has problems looking toa cell on a different sheet...

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
Range("b4:b" & Range("'claas entry'!f4").Value).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

 

[dukeofscouts]

Correction error 1004 sorry...

New error: 104: _Global
Looks like it has problems looking toa cell on a different sheet...

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
Range("b4:b" & Range("'claas entry'!f4").Value).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