Searching for data in column, if found Cut it paste on other sheet

[Theglyde]

I want to search Column "b" for "98" and if found (could be multiples line) I want those lines to be cut and pasted in a new sheet called "Diesel Discount"
This is what I got so far. If it Highlighs the first End With as error...

Sub Move_Diesel_Discount()
Dim LR As Long, i As Long
With Sheets("Discount Sheet")
LR = .Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("B" & i)
If .Value = "98" Then
.EntireRow.Cut Destination:=Sheets("Diesel Discount").Range("A" & Row.Count).End(xlUp).Offset(1)
End With
Next i
End With
End Sub

 

[Joe4]

OK, I will try this one more time.

You have posted the line that is highlighted when the error occurs, but NOT the actual error message that pops up to the screen. I need that!

It looks something like this:

That message is important, as it gives us a clue as to what exactly is going on.

 

[Theglyde]

I apoligize for that, here it is

 

[Joe4]

Thank you.

OK, 2 things:

1. Which module did you put this code in? You should put it in a General module, and not one of the Sheet or Workbook modules.
If you follow steps 1-4 here, it will show you how to create a new General module in your workbook: Insert and run VBA macros in Excel - step-by-step guide

2. Let's add some error handling code to the VBA code that will tell us which row the error is occurring on. Copy/paste this VBA code and run it, and tell me what the message box that pops up says:

Sub Move_Diesel_Discount()

    Dim LR As Long, i As Long

    On Error GoTo err_chk
    
    With Sheets("Discount Sheet")
        LR = .Range("B" & .Rows.Count).End(xlUp).Row
        For i = 1 To LR
            With .Range("B" & i)
                If .Value = 98 Then
                    .EntireRow.Cut Destination:=Sheets("Diesel Discount").Range("A" & Rows.Count).End(xlUp).Offset(1)
                End If
            End With
        Next i
    End With
    
    On Error GoTo 0
    Exit Sub
    
    
'Error handling code
err_chk:
    If Err.Number = 9 Then
        MsgBox "The value of i when error occurs is: " & i
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub

 

[Theglyde]

I will verify this after but yes it is in general, I ran a quick test and here is what I did and maybe it can help you help me like you said.

When I start at the bottom I have Paste Sheet Here and Macro button Sheet
when I run my macro it creates a sheet called Discount sheet on its own which is where the Macro we are doing is taking data from. IT should take data and copy to Diesel Discount that does not exist yet.

My test is that I created a sheet called Diesel discount before running the macro and it worked.

My question is how do I get it to create the sheet Diesel Discount automatically before trying to paste the info to if. Hopefully this makes sense

 

[Joe4]

when I run my macro it creates a sheet called Discount sheet on its own which is where the Macro we are doing is taking data from. IT should take data and copy to Diesel Discount that does not exist yet.

Well, that is a very critical detail to know! Of course you will get an error if the sheet does not exist at the time when the code is run!

I am a little confused by your explanation. There is nothing in the original code you posted that is creating any sheets.
So how/when exactly is this sheet being created?
If it is being created by other VBA code, please post that code (being sure to let us know exactly how & when that code is run).

 

[Theglyde]

Well, that is a very critical detail to know! Of course you will get an error if the sheet does not exist at the time when the code is run!

I am a little confused by your explanation. There is nothing in the original code you posted that is creating any sheets.
So how/when exactly is this sheet being created?
If it is being created by other VBA code, please post that code (being sure to let us know exactly how & when that code is run).

that is the code I am missing, how would I add this that if there is a 98 in column B to create the sheet Diesel Discount and then copy the rows in it. That code I do not see how to incorporate it in here. Sorry again for the confusion

 

[Joe4]

OK, note the section in red that I added in:

Sub Move_Diesel_Discount()

    Dim LR As Long, i As Long

    With Sheets("Discount Sheet")
        LR = .Range("B" & .Rows.Count).End(xlUp).Row
        For i = 1 To LR
            With .Range("B" & i)
                If .Value = 98 Then
'                   Check to see if sheet exists
                    If Evaluate("ISREF('Diesel Discount'!A1)") = False Then
'                       If not, add it
                        Worksheets.Add.Name = "Diesel Discount"
                    End If
'                   Copy row to other sheet
                    .EntireRow.Cut Destination:=Sheets("Diesel Discount").Range("A" & Rows.Count).End(xlUp).Offset(1)
                End If
            End With
        Next i
    End With
   
End Sub

 

[Theglyde]

OMG THANK YOU it finally works and totally my bad if I was not clear as I said my brain as not been working as usual this week.

Again THANK YOU!!!!

It is totally appreciated

 

[Joe4]

You are welcome.
Glad we were able to get it all sorted out.

 

[Theglyde]

Question regarding this code, if instead of looking for the value 98 specifically I wanted to search for anything between 510000 & 510010 and transfer those rows, how would I go about to doing so
how would I search all in one shot?