Excel, Selection of rows to copy/ then to WORD/FrontPage

  • I am trying to figure out how to select multiple rows, based upon a variable input by user.
    I have the following code:

    usedV = Application.InputBox("Enter Number of Used Vehicle: ", 2)
    Cells.Find(What:=usedV, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    Rows("141:154").Select ' <---------------I know this needs to change, not sure
    End Sub

    What I wish to do is have the user input a Stock#(i.e.4816g), then the macro will find the stock number, and select the rows pertaining to it, until it reaches another stock number. Then I want to copy the rows (i.e. Rows 128:140)to cell A33 pane1. Then I need to copy the data in cells A1:B30 to WORD, and then copy the data from WORD into a table into FrontPage. If you are wondering why I go to WORD at all, it is because I have not figured out how to keep the formatting in Excel to carry over into FrontPage directly. When I go to WORD, then FrontPage, the cell font, formatting, and color stays the same.

    I am including the Excel doc, for reference, because, it might be easier to understand. Thank you very much for any guidance.

    I have learned a lot from this group, but have not dealt with any FrontPage VB yet; any good references would be appreciated. I have not found any yet. This site is the BEST!

  • Will it always be the same number of rows between stock numbers?

    if so, you may be able to do something like:
    (if it is alwas 13 rows)

    fr = ActiveCell.row
    Rows(fr & ":" & fr+13).Select

    This sets the first row to the active cell (found by your Cells.Find code). and then sets the last row 13 rows later.

    If it is not the same, you may need to do another find to determine the last row. (i.e. is there a blank row between each set of entries)

Participate now!

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