Running a macro from a home sheet?

[Joshua88]

Hello all I'm new here, and seem to stuck on some code, I have a sheet called home with my macro buttons, I also have a hidden sheets called datadump3, that I use to
import data from another work book, that all works fine but I am having an issue with the last part were I am deleting rows that contain Do Not Cut, on datadump3, when I'm on the home sheet and click the macro button I get an error (select method of range class failed), but if I select on the datadump3 sheet and make it the active sheet and run the code from vb it runs fine, I think I need to specify which sheet it should be working on? not sure any help would be great hope I posted this right thank you

   Sub GtSt()
'
' GtSt Macro
'
'===================================================
'define the variables
On Error GoTo Errmsg
Errmsg:
    If Err.Number = 9 Then
        MsgBox "The workbook named cutlist is not open or has been renamed. Click ok then in the eCabinets cut list click on export cut list to excel and select open.", vbCritical
        Exit Sub
 
    End If
Application.ScreenUpdating = False
Dim Home As Worksheet
Set Home = ThisWorkbook.Sheets("Home")
Dim eCabCl As Workbook
Dim SheSts As Worksheet
Dim Ddump3 As Worksheet
Set eCabCl = Workbooks("cutlist.xls")
Set SheSts = eCabCl.Sheets("SheetStockSummary")
Set Ddump3 = ThisWorkbook.Sheets("DataDump3")
Dim col As Variant
Dim isnum As Boolean
'===================================================
'Import theBuild in house Drawer Box Listing Cutlist from ecabinets into the data dump worksheet
'Clear existing data
Ddump3.UsedRange.ClearContents
SheSts.UsedRange.Copy Destination:=Ddump3.Range("A1")
'Convert numbers stored as text to numbers
Ddump3.Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
     Ddump3.Range("H:H").WrapText = False
'will not wrap test
Ddump3.UsedRange.WrapText = False
'Now loop thru the columns and use text to columns to convert the numbers that are store as text to numbers
For Each col In Ddump3.Columns
    'exit the loop if there is no data in the current column header
    If Ddump3.Cells(1, col.Column) = "" Then
        Exit For
    Else
      'Or else just do text to columns to convert the numbers stored as text to numbers notice every thing is set to false
        Ddump3.Columns(col.Column).TextToColumns Destination:=Cells(1, col.Column), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1)), TrailingMinusNumbers:=False
        isnum = IsNumeric(Ddump3.Cells(2, col.Column))
            If isnum = True Then
                If Ddump3.Cells(1, col.Column) = "Qty" Then
                    Ddump3.Columns(col.Column).NumberFormat = "0"
                Else
                    Ddump3.Columns(col.Column).NumberFormat = "0.00"
                End If
              End If
  
    End If
Next col
'Call RemoveLines
'============================================================================================================
'delet do not cut
Worksheets("DataDump3").Range("B1").Select
For Counter = 1 To 12
If ActiveCell.Value Like "*Do Not Cut*" Then
ActiveCell.EntireRow.Delete ' Delete Row
Else
ActiveCell.Offset(1, 0).Select ' Move down a row
End If
Next
Application.ScreenUpdating = True

End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

'delet do not cut
With Worksheets("DataDump3")
   For counter = 12 To 1
      If .Range("B" & counter).Value Like "*Do Not Cut*" Then
         .Rows(counter).EntireRow.Delete ' Delete Row
      End If
   Next
End With
Application.ScreenUpdating = True

 

[Joshua88]

Hi thanks for the help, well I did not get an error but it is not deleting rows that contain do not cut, any idea?

 

[Fluff]

Do any cells in B1:B12 contain the phrase "Do Not Cut"?

 

[Joshua88]

Yes I noticed that when I changed 12 to 1 , to 1-12 it deleted one of them I will try to send a picture

 

[Fluff]

My mistake, it should be

   For counter = 12 To 1 [COLOR=#ff0000]Step -1[/COLOR]

 

[Joshua88]

Perfect thank you so much I have spent about 5 hours trying to figure that out thank you

 

[Fluff]

Glad to help & thanks for the feedback

 

[Joshua88]

one other question not sure if I should repost a new thread? but I want to delete columns c and e, I'm having the same issue with it running on my home page

'delete do not cut
With Worksheets("DataDump3")
   For Counter = 12 To 1 Step -1
      If .Range("B" & Counter).Value Like "*Do Not Cut*" Then
         .Rows(Counter).EntireRow.Delete ' Delete Row
      End If
   Next
End With
  
'=======================================================================
'Delete Columns
Columns("C").EntireColumn.Delete
Columns("E").EntireColumn.Delete

Application.ScreenUpdating = True

 

[Fluff]

Try

With Worksheets("DataDump3")
   For counter = 12 To 1 Step -1
      If .Range("B" & counter).Value Like "*Do Not Cut*" Then
         .Rows(counter).EntireRow.Delete ' Delete Row
      End If
   Next
  
'=======================================================================
'Delete Columns
   .Columns("E").EntireColumn.Delete
   .Columns("C").EntireColumn.Delete
End With