0

I am trying to copy data from the 2nd to last worksheet and paste it onto the last worksheet but I don't know how to get the worksheet name of the 2nd to last sheet:

Public Sub CNPPrevOOS()
' Previous day out of stock items
    Worksheets(**ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count - 1)**).Select

    c = Worksheets(**ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count - 1)**).Cells(Rows.Count, 1).End(xlUp).Row

    ' Filters the data where column 2 = x
    ActiveSheet.Range(Cells(1, 1), Cells(c, 2)).AutoFilter field:=2, Criteria1:="x", Operator:=xlFilterValues

    ' Selects only the filtered cells and copy
    Range(Cells(2, 1), Cells(c, 1)).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

    ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count).Select
    ActiveSheet.Paste Destination:=Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
t l n
  • 25
  • 4

1 Answers1

0

To get the name of the 2nd to last sheet you can use:

Debug.Print Sheets(Sheets.Count - 1).Name

Implementing this in your code (without using Select) looks something like this:

Sub Shelter_In_Place()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lr1 As Long, lr2 As Long

Set ws1 = ThisWorkbook.Sheets(Sheets.Count)       'Last Worksheet
Set ws2 = ThisWorkbook.Sheets(Sheets.Count - 1)   'Second to Last Worksheet

lr1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Offset(1).Row
lr2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row

ws2.Range("A1:B" & lr2).AutoFilter Field:=2, Criteria1:="x", Operator:=xlFilterValues

ws2.Range("A2:B" & lr2).SpecialCells(xlCellTypeVisible).Copy
ws1.Range("A" & lr1).PasteSpecial xlPasteValues

End Sub
urdearboy
  • 14,439
  • 5
  • 28
  • 58