Find date, copy row above, multiple worksheets

**DAK266** · 10-01-2012, 11:32 AM

[SOLVED]

Hi all,

I posted on this recently and thought we came to a solution, but it does not seem to be working. I have a variable called "mmonth" that is the date. The dates, in identical formats, are listed in column A of various worksheets. I have a code that searches through a worksheet and finds the date, then copies the row above it to the current row, and renames the cell in column A back to the current date.

Sheets("Sheet1").Select
Cells.Find(What:=mmonth, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).EntireRow.Copy Destination:=ActiveCell
Sheets("Inputs").Range("A4").Copy Destination:=ActiveCell

When I try the same code after it with a Sheet2 replacing Sheet1, the error I receive tells me that the sheet is out of range. This macro is contained in "Module 1," so it should work for the entire workbook.

The same error comes when I try this code, which was suggested by another user on this site:

Dim FoundIt As Range
    
            Set FoundIt = Sheets("Sheet2").Cells.Find(What:=mmonth, After:=ActiveCell, _
                                                     LookIn:=xlFormulas, LookAt:=xlPart, _
                                                     SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                     MatchCase:=False, SearchFormat:=False)
        
                If Not FoundIt Is Nothing Then
                    FoundIt.Offset(-1, 0).EntireRow.Copy Destination:=FoundIt.EntireRow
                Else
                    MsgBox "The Date " & mmonth & " was found.", vbExlam + vbOKOnly
                End If
            
            Sheets("Inputs").Range("A4").Copy Destination:=FoundIt

Any insight to why this isn't working for sheets2, 3, and 4... would be appreciated.

Thanks!