vba to find specific text in a column and then when found copy all the rows above it

[JKK22]

I'm looking for vba help. I have a source workbook that is stored and saved and I also have an actively open workbook. (my destination workbook)

Heres what I want to code:
1. Find specific text in a column C ="Grand Total" and then when it's found, copy the rows above the row that grand total is in. The data to copy should be only from columns B-L , from the last row up to & including row 9 (because rows 1-8 have header data) .
2. Then paste special values and number formats into the destination workbook starting in cell A4

In other words- in the source workbook, the row above the row with "Grand Total" found in column C will be the last row to copy
The data to copy is Copy the data in columns B-L starting on row 9 of the source workbook and continue copying all the rows , but stopping when it finds "Grand Total" in column C.

 

[mumps]

Place this macro in a regular module in your source workbook. Change the sheet names (in blue) and the workbook name (in red) to suit your needs.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, fnd As Range
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    Set desWS = Workbooks("Destination.xlsx").Sheets("Sheet1")
    With srcWS
        Set fnd = .Range("C:C").Find("Grand Total", LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            Intersect(.Rows("9:" & fnd.Row), .Range("B:L")).Copy
            desWS.Range("A4").PasteSpecial xlPasteValues
            desWS.Range("A4").PasteSpecial xlPasteFormats
        End If
    End With
    Application.ScreenUpdating = True
End Sub