VBA: Selecting sheets without naming them

[DrH100]

Hi

I have some code in my workbook which basically looks for the letter Y in column I and copies that data from sheet "Julie" to the "archive" sheet.

What I would like to do is to replace the references to Sheets("Julie") and enable it to run from whatever worksheet I am running the macro from. I've tried using ActiveSheet but when the Archive is closed down it won't reselect the original worksheet.

Does anybody have any ideas how I may do that.

The code that I am currently using is

Public Sub CopyRows()
Application.ScreenUpdating = False
Sheets("Archive").Visible = True
    Sheets("Julie").Select
    ' Find the last row of data
    FinalRow = Range("A65536").End(xlUp).Row
    ' Loop through each row
    For x = 2 To FinalRow
        ' Decide if to copy based on column H
        ThisValue = Range("i" & x).Value
        If ThisValue = "Y" Then
            Range("A" & x & ":i" & x).COPY
            
            Sheets("Archive").Select
            NextRow = Range("A65536").End(xlUp).Row + 1
            Range("A" & NextRow).Select
            ActiveSheet.Paste
            Sheets("Julie").Select
            Rows(x).delete
       
        End If
    Next x
    Sheets("Archive").Visible = False
    Application.ScreenUpdating = True
End Sub

Any help appreciated

 

[p45cal]

try:

Public Sub CopyRows()
Application.ScreenUpdating = False
With ActiveSheet
  ' Find the last row of data
  FinalRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  ' Loop through each row
  For x = FinalRow To 2 Step -1
    ' Decide if to copy based on column H (well.. I actually).
    If .Range("i" & x).Value = "Y" Then
      .Range("A" & x & ":i" & x).Copy Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "A").End(xlUp).Offset(1)
      .Rows(x).Delete
    End If
  Next x
End With
Application.ScreenUpdating = True
End Sub

A few things to note.
1. No sheets are selected/deselected. No cells are selected/deselected.
2. You don't need to make Archive visible, then invisible again.
3. Moving down the rows of the active sheet will miss rows because you're deleting as you go, and rows shift up. If Y was present in column I in two rows next to each other, the lower one would be missed. So the code has been changed to move up through the rows. This means that the order in which rows are pasted to Archive are in reverse order. If this is a problem, the code can be changed to use a filter for Y on column I and all rows copied over and deleted in one shot.

 

[DrH100]

Thanks very much p45cal. That works fantastically