Help with Error Message Box code
 

Hi

I am working on a macro to do some copying and pasting but it relies o
the active cell of the destination sheet being blank and in a correc
column. I need help with code to bring up an error message if th
active cell is in a wrong column or has data in it. The active cel
must be in any of several columns which has a value of 1 in row 4.

My macro was created in part by the recorder and edited by me (and b
some miracle it works). I am obviously very new at this so I woul
also be grateful to hear suggestions on cleaning it up, please. Her
is what the macro is now:

Sub Macro6()
Application.Goto Reference:="scores"
Selection.Copy
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
ActiveCell.Select
Application.CutCopyMode = False
ActiveCell.Offset(0, -6).FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(-1, -5).Select
Selection.Resize(1, 5).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 5).Select
End Sub


Thanks in advance for any help

--
Message posted from http://www.ExcelForum.com

Help with Error Message Box code
 

The real question is ... what do you REALLY want to do if the activ
cell on Sheet3 is not blank? All you really want is a message box?
OK, here it is. But, better if you go to Sheet3 and select a cel
instead of just letting the macro find its own active cell.

Sub Macro6()
Application.Goto Reference:="scores"
Selection.Copy
Sheets("Sheet3").Select
If activecell = "" then
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False
ActiveCell.Select
Application.CutCopyMode = False
ActiveCell.Offset(0, -6).Value = Date
ActiveCell.Offset(-1, -5).Select
Selection.Resize(1, 5).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 5).Select
Else:
MsgBox "hey, rummy, this cell ain't blank!"
End if
End Su

--
Message posted from http://www.ExcelForum.com

Help with Error Message Box code
 

Thanks for the reply MSP77079.

What I want to do is stop the pasting of any data over existing data s
what I REALLY want to do if the 2 conditions aren't met is to have th
macro just activate Sheet3. I will then make the correct cell active
return to the original sheet and run the macro again.

I don't see anything in your code to deal with the second conditio
that needs to be met (the value in Row 4 of the active cell's colum
must have an existing value of 1 and there are 10 columns meeting tha
condition). Since I don't know how to deal with an AND could you hel
further with that part?

Thanks again for the help so far

--
Message posted from http://www.ExcelForum.com