VBA>! Search all sheets for the value contained in sheet "R" "F9", within the found Sheet, find cell "F11" of sheet "R"

[ronald54457874]

As I'm new to VBA, I'm studying new possibilities, and as I'm new to the forum, I'll learn a lot from all of you. I was making some adaptations to a partner's code, all of which worked until I was stopped in this one.

*This is not necessary, it's just for knowledge and study purposes, if you don't want to answer, no problem!*

Let's go?

I need to create a delete button, and delete what?

I currently have a macro that inserted a new row in the spreadsheet created by the previous macro, and in this spreadsheet created it is renamed by the cell "Register""F9", so I need that for each row that is created if the user wants to delete then he can only delete the last one line created by the previous macro.

How to do this?
I intend to search in all sheets for the name of the sheet I want with a code that goes to cell "F9" of sheet "Register" and looks in all sheets for cell "Register""F11" that is the information I want. Because my previous macro it creates the spreadsheet, renames it to "Register""F9" and then I inserted a new line by copying the data from the "Register""F11" cells and pasting in the last line of the new spreadsheet or the existing spreadsheet (my macro it already searches if it has the created or not the Sheets).

As I said I just need to search if there is the data of the cell "Register""F11"in the spreadsheet created and renamed by "Register""F9" if there is it will delete the last row created, and if you want to delete more it will delete more rows created until there is nothing left, but it has to stop at "A3:I3" because this is the header of my Spreadsheet.


My code to delete so far:

Sub Excluir_Marcas()

 
 
  Dim controlSht As Worksheet
  Dim outPerson1 As String
  Dim outPerson2 As String
  Dim OutRow As Long
 
    Set controlSht = Worksheets("Register")
    outPerson1 = controlSht.Range("F9").Value
 
    Set controlSht = Worksheets("Register")
    outPerson2 = controlSht.Range("F11").Value
   
   On Error Resume Next
    Set outSht = Worksheets(outPerson)
    On Error GoTo 0
 
   On Error Resume Next
    Set outSht2 = Worksheets(outPerson2)
    On Error GoTo 0

The code is this, now it needs to finish searching all sheets for "F9" and inside Sheet "F9" search for "F11". The information "F11" will be contained in column "A". This is a VLOOKUP, but not VBA. I tried but couldn't, thanks everyone!

 

[Alex Blakenburg]

I don't know how you want to determine what rows to delete if you want to do multiple rows but if you just want to delete the last row entered eg the same criteria that are still on the Add Row sheet then this might work for you.

    Dim controlSht As Worksheet
    Dim outSht As Worksheet
    Dim outRow As Long
    Dim outPerson As String
    
    Set controlSht = Worksheets("Register")
    outPerson = controlSht.Range("F9").Value
    
    On Error Resume Next
    Set outSht = Worksheets(outPerson)
    On Error GoTo 0
    
    If outSht Is Nothing Then
        MsgBox "The sheet for " & outPerson & " does not exist"
         Exit Sub
    End If
    
    outRow = outSht.Cells(Rows.Count, "A").End(xlUp).Row
    
    If outSht.Cells(outRow, "A").Value = controlSht.Range("F11").Value Then
        outSht.Cells(outRow, "A").EntireRow.Delete
        outRow = outRow - 1
    End If
    
    Application.DisplayAlerts = False
    If outRow = 4 Then outSht.Delete
    Application.DisplayAlerts = True
    
End Sub

 

[ronald54457874]

You're the man hahaha, I've been trying to do this all day. I'm going to see these udemy courses there in ????? congratulations know a lot ??

 

[Alex Blakenburg]

No worries. Glad I could help. Thanks for letting me know.