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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 24, 2017
Messages
129

ADVERTISEMENT

Gotcha Thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

kenpcli

Board Regular
Joined
Oct 24, 2017
Messages
129

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Forum statistics

Threads
1,136,434
Messages
5,675,844
Members
419,586
Latest member
RoteichA

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
Top