none
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.

    Thanks.
    Monday, September 21, 2009 5:23 AM

Answers

  • 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 = xl.workbooks.open("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
    wbSrc.worksheets(1).range("2:2").copy
    wbDest.worksheets(1).range("2:2").pastespecial


    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.

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