Posts by JFaust

    Re: Copying Instances From One Workbook To Another


    Thanks Bill I replaced lines 58 and 59 with what you gave me but I'm getting another runtime error.


    Runtime error '9'
    Subscript out of range


    Again it highlights lines 58 and 59

    Re: Copying Instances From One Workbook To Another


    when I compile the code is comes up with a runtime error in line 59


    runtime error '438'
    object doesn't support this property or method




    Bill to answer your question line 34 calls a macro in the OilSAMPLE.xls that updates the OilSAMPLE spreadsheet.

    Code
    1. Application.Run "OilSAMPLE.xls!UpdateData" 'Run the Get Info Macro


    The code for that macro is pretty simple

    Code
    1. Sub UpdateData()
    2. ActiveWindow.SmallScroll ToRight:=-4
    3. Range("C1").Select
    4. Selection.QueryTable.Refresh BackgroundQuery:=False
    5. End Sub

    I have two workbooks, a "vehicle workbook" and an "Oil sample" workbook. I'm trying to write a macro that will store the ID of the vehcile in "vehicle workbook" into a varible, open the Oil sample workbook and find instances of the vehicle ID in column B. Next it will copy the entire row, and paste it back into the "vehicle workbook". I've taken some code and modified it to try and make it work but I'm running into problems. I'm very much a newbie to excel and macro's and am learning little by little. This is what I have so far...



    Thanks!
    Jeff

    Question: Is there a way to display the location of an active workbook on my local drive and save it into a varible. I'm new to Excel and VBA but I'm trying to create a template to use for multiple vehicles. When the macro is run...


    1) the workbook will have to identify what Workbook and worksheet it is in and save it as a varible so that it can access it later. Each workbook is specific to a vehicle.
    *(The reason is because I will need to refer to the address of the original workbook when pasting the copied data from the "OilChart" Workbook.)


    2) Copy the vehicle identification # and save as a varible. (Dim vehicleNumber As Variant)


    3) Open another workbook on the local drive (OilChart.xls)


    4) Run the macro in the new workbook (OilChart.xls) to update the data


    5) Search Column B in (OilChart.xls) for matches of the vehicleNumber, and for all rows that match, select and copy all rows and paste it back into the orig workbook (Vehicle Specific Workbook).


    This is the code I have so far. Any help would be appreiciated greatly. Please remember I'm a newbie so be discriptive. Thanks!



    I have'nt figured out how to do step 5 yet so if anybody has any ideas I'd be open to them. I tried looking at Aaron Bloods Kickbutt VBA Find Function but I got a little lost on how his code would relate to my code. It also looks like it doesn't copy or paste anything to another workbook. Again any help would be appreciated.


    Thanks!
    Jeff