VB script needed RRS feed

  • Question

  • Hi,
    I would require a VB script which can search for a content in an excel sheet and copy the same row contents and paste it in an new excel file.
    Kindly advice.

    Monday, September 21, 2009 5:23 AM


  • Hello ocwen,

    Can you provide a sample or description of your excel sheet and the content to be searched? It will make the decision of how to search much easier.

    I tend to use excel templates instead of just pasting in a new workbook. This way if I need to add formulas, formatting, charts, logos, etc, I only need to change the template and usually not the script.

    As for copying row contents and pasting it in a new excel file, try this...

    set xl = createobject("excel.application")
    xl.visible = true
    set wbSrc ="c:\scripts\book1.xls")
    set wbDest = xl.workbooks.add()
    'using range.value
    wbDest.worksheets(1).range("1:1").value = wbSrc.worksheets(1).range("1:1").value
    'using copy and paste

    PasteSpecial takes optional parameters which are really cool (in a geeky sort of way :-)
    Also, try adding formulas and formatting in the cells in c:\scripts\book1.xls to see the difference between range.value and paste.

    • Marked as answer by IamMred Saturday, January 9, 2010 2:12 AM
    Tuesday, September 22, 2009 4:19 AM