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

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

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