Results 1 to 1 of 1
  1. #1
    Join Date
    May 2014
    Provided Answers: 2

    Unanswered: Macro works every other time

    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

    ActiveCell.PasteSpecial (xlPasteValues)
    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?

    Sub MacroRow2()
    ' MacroRow2 Macro
    Range("B2:C2").Delete Shift:=xlUp
    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
    Next Cell
    ActiveCell.PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    End Sub
    Last edited by Doug60M; 02-27-16 at 09:45.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts