Selecting rows between 2 cell values

  • I have written code in excel to search and open a file, once the file is open it will copy and past information from one workbook into another. I do not want the entire sheet copied and pasted, I would like to have only certain rows selected.

    I want the rows below the header row of row 8 to be copied but to stop once it reaches the cell with the value in it of Reports Total. This could be anywhere from 5-2000 rows to be selected. Is there a way to write a For/Then loop to copy the information in the rows until it gets to the cell that has the words Reports Total:.

    This is what I have so far:

        ActiveWindow.WindowState = xlMinimized
        ActiveWindow.WindowState = xlMaximized

    Any assistance would be greatly appreciated.

  • Re: Selecting rows between 2 cell values

    Is the cell with "Reports Total" the last used row of the sheet? Also, if you could provide a sample sheet of data it would be easier for someone to assist you, as well as the expected result.

  • Re: Selecting rows between 2 cell values

    This should do it, or get you close

    Sub this()
    Dim rng As Range
    Dim Rpttotals As Long
    With Sheets(1)
    Rpttotals = Application.WorksheetFunction.Match("Report Totals:", .Range("A:A"), 0) - 1
    Set rng = .Range("A9:F" & Rpttotals)
    End With
    Sheets(2).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
    End Sub
  • Re: Selecting rows between 2 cell values

    Would this still work if I added it to code that I have setup to open a file? Please see code example below:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!