Object Error

kenpcli

Board Regular
Joined
Oct 24, 2017
Messages
129
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top