I have a worksheet 1 where I keep track of staff getting mandated to stay. It is done on a rotating basis. The worksheet uses 2 columns, B for the name and C for the date and time they were mandated to stay.
The Macro selects the staff name moves it to the bottom of the list deletes the cells the name was in and shifts the cells up and places the current date and time in column C. This works great.
the problem I am having is trying to keep a historical record of all the times someone was mandated to stay on Worksheet 2. All I need is to copy the last record, open worksheet 2 look for the 1st open cell in column A and paste the contents.
The funny thing is this works every other time. Works fine does what I want, then if I run the Macro a 2nd time it fails at the
Run Time Error '1004'
PasteSpecial method of Range Class failed.
hit the Debug button, close debugger without making any changes, then run the macro and it works fine.
Continues like this failing every other time.
What is going on?
' MacroRow2 Macro
Range("C6") = Now
Dim ws As Worksheet
Set ws = ActiveSheet
For Each Cell In ws.Columns(1).Cells
If IsEmpty(Cell) = True Then Cell.Select: Exit For
Application.CutCopyMode = False