vba Found text cell address as last row range row

[Rowland Hamilton]

Folks:

Looking to modify your code to find the row with "Grand Total" in column B, then

Range("A2:H" & LstRow).Copy

I tried this code, it only copied 2 rows, definitely not the row with "Grand Total" found in it.

How can I fix it?

Thank you, Rowland:

Sub try_this()
    
'copy
        Workbooks("United States (de linked)xxxx.xlsm").Activate
            Sheets("500-Sample").Activate
    
    'Expand Column groups, Collapse Row groups
                ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2
  
          
                'contiguous method won't work:
          'Range("A3").Select
          'Range(Selection, Selection.End(xlDown)).Select
          'Range(Selection, Selection.End(xlToRight)).Select
          
        LstRow = Application.WorksheetFunction.Match("Grand Total", ActiveSheet.Range("B:B"))
            Range("A2:H" & LstRow).Copy
'paste
        Workbooks("Incoming_Data.xlsm").Activate
          Sheets("Master-Incoming").Activate
          Range("B65000").Select
          Selection.End(xlUp).Select
          ActiveCell.Offset(1, -1).Select
          
                Selection.PasteSpecial Paste:=xlPasteValues, _
          Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Selection.PasteSpecial Paste:=xlPasteFormats, _
          Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
          Workbooks("United States (de linked)xxxx.xlsm").Activate
          
                'These two maybe not necessary bc don't save this wb:
          Application.CutCopyMode = False
          Range("B1").Select
End Sub

 

[Rowland Hamilton]

Folks:

Forgot I had this method from a macro I used a couple of years ago. Only really finds last cell, not last cell with specific text:

'Determine last copy row of sheet<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
                Range("p1").Select<o:p></o:p>
                ActiveCell.Formula = "=(65536-COUNTBLANK(A:A)-4)"<o:p></o:p>
                RowCount = ActiveCell.Value<o:p></o:p>
                If RowCount = "0" Then<o:p></o:p>
                Else<o:p></o:p>
                    'ActiveWorkbook.Unprotect "swordfish" password specific<o:p></o:p>
                    'Copy<o:p></o:p>
                    Range(Cells(10, 1), Cells(RowCount + 9, 15)).Copy<o:p></o:p>

 

[Rowland Hamilton]

Folks:

I'm looking for the first (and only) "Grand Total" in column B. There are populated cells underneath the "Grand Total" so row counting is not enough. I can add a formula If I find that Grand Total, offset 1 row down and make that cell = row formula for the found cell:

    Range("B1").Select
    Columns("B:B").Select
    Selection.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(1, 0).Activate
    ActiveCell.NumberFormat = "General"
    ActiveCell.Formula = "=ROW(R[-1]C)"
    ActiveCell.Copy
 
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Was trying to do this without relying on cell formulas in case the source doc changes and the cells I'm typing in are now in use or locked.