Insert data into spread sheet after each clients name?

[tonywatsonhelp]

Hi Everyone,

I need a macro that will do the following.

In sheets "Control" take the list a clients names from Column G,
Go To "Bookings" sheet and look for each clients name down column 1.

Now each client will have multiple entries so we need to find their last row.
once found insert a row below it and run my macro "Tonys Data"


now "Tonys data" needs to know what rows was just inserted so if you can record the inserted row as "Insrow" that would be great (i can deal with from there lol)

then do the same with the next and the next until they are all done.

if any name is not already in the List run macro on lastrow +1

Thanks

Tony

 

[Yongle]

Re: Instert data into spread sheet after each clients name?

Try this
"XXX" placed in cell in column A of inserted row

In Standard module

Sub Test()
    Dim wsC As Worksheet, wsB As Worksheet
    Dim cel As Range, rng As Range, foundRow As Range, myNames As Range
    Dim aName As String
    
    Set wsC = Sheets("Control")
    Set wsB = Sheets("Bookings")
    Set myNames = wsC.Range("G2", wsC.Range("G" & Rows.Count).End(xlUp))
    
    For Each cel In myNames
        aName = cel.Value
        Set rng = wsB.Range("A:A")
        On Error Resume Next
        Set foundRow = rng.Find(what:=aName, after:=rng.Cells(1), searchdirection:=xlPrevious, Lookat:=xlWhole).Offset(1)
           If Not foundRow Is Nothing Then
                wsB.Rows(foundRow.Row).Insert
                Call DavesData(foundRow.Row - 1)
                Set foundRow = Nothing
            Else
                Call DavesData(wsB.Range("A" & Rows.Count).End(xlUp).Offset(1).Row)
                On Error GoTo 0
            End If
    Next cel

End Sub

Private Sub DavesData(InsRow As Long)
    Sheets("Bookings").Cells(InsRow, 1) = "XXX"
End Sub

 

[tonywatsonhelp]

Re: Instert data into spread sheet after each clients name?

Thanks Yongle this is great Tony