Using Sheets().Select in a Worksheet_Activate() macro

[jdun]

Hey all! I'm trying to write a code which copies one row from an INPUT worksheet, once a certain cell has one of three specific phrases in it. Once copied, the macro would move the entire row (columns A - D) over to a separate worksheet (named OUTPUT). The macro would then fill in the date at which this process was done in the designated cell, and then clear everything but column A of cell in the INPUT sheet.

I had my VBA working, or so I thought. It worked when it ran independently, but as soon as I tried switching it over to trigger on Worksheet_Activate, I'd get errors.

I'm wondering if it's even possible to do this, any help would be much appreciated. I'll paste my code in the post.

This is my very first attempt at VBA, this was all done in a week, by scouring forums and the microsoft help page. Thank you for any help in advance!

Sub Project_Complete()

' defines variables lr and i as long variable type
Dim i As Long

 ' forces screen to stop updating, which means it wont flicker while script runs
Application.ScreenUpdating = False

' this is checking each row of column D (column 5), moving up to row 3, sets i = the row number
    For i = Sheets("INPUT").Columns(5).Find("*", , xlValues, , xlByRows, xlPrevious).Row To 3 Step -1

     ' If any cell in row i column 5 of INPUT sheet has the word "finished", "failed", or "aborted"
        If Sheets("INPUT").Cells(i, 5).Value = "complete" Or Sheets("INPUT").Cells(i, 5).Value = "failed" Or Sheets("INPUT").Cells(i, 5).Value = "aborted" Then

            'Copies entire row from input
            Sheets("INPUT").Cells(i, 5).EntireRow.Copy

            'Shifts entire row down, copies format of row below
            Sheets("OUTPUT").Range("A3").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

            'Pastes row data
            Sheets("OUTPUT").Cells(3, 1).PasteSpecial xlValues

            'Pastes row format
            Sheets("OUTPUT").Cells(3, 1).PasteSpecial xlFormats

            'Should add a date to D3 cell in OUTPUT worksheet
            Sheets("OUTPUT").Select
                With Range("D3")
                    .Value = Date
                    .NumberFormat = "mm/dd/yy"
                End With

            'Deletes everything but first column of i row
            Sheets("INPUT").Cells(i, 5).Clear
            Sheets("INPUT").Cells(i, 4).Clear
            Sheets("INPUT").Cells(i, 3).Clear
            Sheets("INPUT").Cells(i, 2).Clear

            ' clears clipboard to stop marching ants
            Application.CutCopyMode = False
            
       End If

 ' moves to next row in the i loop
    Next

 ' stops screen flicker
Application.ScreenUpdating = True

End Sub

 

[RoryA]

You don't need to select the sheet at all. Change this:

           'Should add a date to D3 cell in OUTPUT worksheet
            Sheets("OUTPUT").Select
                With Range("D3")
                    .Value = Date
                    .NumberFormat = "mm/dd/yy"
                End With

to this:

           'Should add a date to D3 cell in OUTPUT worksheet
                With Sheets("OUTPUT").Range("D3")
                    .Value = Date
                    .NumberFormat = "mm/dd/yy"
                End With

 

[jdun]

You don't need to select the sheet at all. Change this:

           'Should add a date to D3 cell in OUTPUT worksheet
            Sheets("OUTPUT").Select
                With Range("D3")
                    .Value = Date
                    .NumberFormat = "mm/dd/yy"
                End With

to this:

           'Should add a date to D3 cell in OUTPUT worksheet
                With Sheets("OUTPUT").Range("D3")
                    .Value = Date
                    .NumberFormat = "mm/dd/yy"
                End With

That seems to at least make the code functional! I am now getting:

"Run-time error '1004':

PasteSpecial method of Range Class failed"

When I hit debug, it highlights this code.

'Pastes row format
            Sheets("OUTPUT").Cells(3, 1).PasteSpecial xlFormats

The code seems to almost work, then break on the very last line. I'll include an image of what the OUTPUT sheet looks like once the error shows up. Any idea on what the fix could be?

 

[RoryA]

I'm confused - that line is before the one that you said was causing the problem, so presumably it was working before?

 

[jdun]

I'm confused - that line is before the one that you said was causing the problem, so presumably it was working before?

Huh, I guess you're right!! I'm actually struggling to replicate my initial error now. It's still giving me the same error, even when I revert the code to the original state. Is there any ideas on how I could fix this error? I'm confused as to why the codes works perfectly up until the very last line it needs to paste. Would it help if I included pictures of both the INPUT and OUTPUT sheets before and after the code is ran?

 

[RoryA]

Do you have any other event code that might be running, such as a Worksheet_Change code?