Object Error

[kenpcli]

Hi Everyone,

I am having trouble on two fronts with this code.

First it doesn't like ("Sht") so the person can identify which sheet to activate.

Second it doesn't like the erow statement.

Dim Sht As String
Sht = InputBox("Please enter the period name")
Worksheets("Sht").Activate
Dim i As Long, myNEW As String, erow As String
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For i = Lastrow To 2 Step -1
myNEW = Cells(i, "R")
If myNEW = "NEW" Then
Cells(i, "R").EntireRow.Cut
Worksheets("HOURS BY ID").Activate
erow = Sheet38.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Pates Destinantion:=Worksheets("Sheet38").Rows(erow)
End If
Next i

Thank you

 

[Fluff]

1) remove the " from Sht, otherwise the code is looking for a sheet called Sht.
2) erow needs to be declared as Long, not String

 

[kenpcli]

1) remove the " from Sht, otherwise the code is looking for a sheet called Sht.
2) erow needs to be declared as Long, not String

The reason I have the " is because of this:

Dim Sht As String
Sht = InputBox("Please enter the period name")
Worksheets("Sht").Activate

 

[Fluff]

That is treating Sht as the name of the worksheet, not the variable that you are trying to use. Variables should never be wrapped in quotes, therefore you need to remove the quotes.

 

[kenpcli]

Gotcha Thank you

 

[Fluff]

You're welcome

 

[kenpcli]

ok so this is the problem I am having that keeps giving me an error: am trying to get the sheet the user defines to look for the word "NEW" in row R and copy and paste that row into the sheet named "HOURS BY ID"

Dim i As Long, myNEW As String, erow As Long
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For i = Lastrow To 2 Step -1
myNEW = Cells(i, "R")
If myNEW = "NEW" Then
Cells(i, "R").EntireRow.Cut
Worksheets("HOURS BY ID").Activate
erow = Sheet38.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Pates Destinantion:=Worksheets("Sheet38").Rows(erow)
End If
Next I

 

[Fluff]

Try

Dim Ws As Worksheet
Set Ws = Sheets(Sht)
If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
Ws.Range("R:R").AutoFilter 1, "NEW"
With Ws.AutoFilter.Range.Offset(1).EntireRow
   .Copy Worksheets("HOURS BY ID").Range("A" & Rows.count).End(xlUp).Offset(1)
End With
Ws.AutoFilterMode = False

 

[kenpcli]

Thank you

 

[Fluff]

You're welcome