Selecting rows between 2 cell values

LJones79 · Dec 7th 2011

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:

Code

Cells.Select
    Selection.Copy
    ActiveWindow.WindowState = xlMinimized
    Range("A1").Select
    ActiveSheet.Paste
    ActiveWindow.WindowState = xlMaximized

Any assistance would be greatly appreciated.

Eggcel · Dec 7th 2011

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.

LJones79 · Dec 8th 2011

Re: Selecting rows between 2 cell values

My apologies, I have attached a sample document and highlighted the the information that I would like to have pulled.

Eggcel · Dec 9th 2011

Re: Selecting rows between 2 cell values

This should do it, or get you close

Code

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
rng.Copy
Sheets(2).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
End Sub

LJones79 · Dec 12th 2011

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:

Code

Sub Button3_Click()
'Open File
    Sheets("Registered User Information").Select
    myMsg = "Load User Information Data?"
    
    Response = MsgBox(myMsg, vbExclamation + vbYesNo, myTitle)
    Select Case Response
    Case Is = vbYes
    Dim origDiruserinfo As String
    Dim NewDiruserinfo As String
    Dim xuserinfo As String
    origDiruserinfo = CurDir
    xezlm = Application.GetOpenFilename(FileFilter:="All Files (*.*),*.*", Title:="Please Browse to the Netsecure List File")
    Workbooks.OpenXML Filename:=xuserinfo
    Case Is = vbNo
    'ActiveWorkbook.Close SaveChanges:=False
    'Case Is = vbCancel
    Exit Sub
    End Select
'Copy Data
    Cells.Select
    Selection.Copy
    ActiveWindow.WindowState = xlMinimized
    Range("A1").Select
    ActiveSheet.Paste
    ActiveWindow.WindowState = xlMaximized
    
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
    rng.Copy
    Sheets(2).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
End Sub

Display More

Selecting rows between 2 cell values

Participate now!

Share