Form Extractor Issue

**Ravenous** · 08-12-2013, 12:06 AM

Hi All,

I have a Macro set up which allows me to extract data from various forms in a folder and paste this data into a single row of a log.
The Macro works great but there is just one problem; I have a cell in my form template for Customer Number which may or may not return a value as some forms do not have a customer number. If this happens and then the next form does have a number then the number will go into the last empty cell of the column because this is how the macro is set up but this means that the number will be in the incorrect row.

In the attached example, Test (5) is returning the Customer Number value for Test (6).

Can anyone please advise on how I can set this up to avoid this problem? My thoughts are to go down on the adjecent column and offset the result to the left but I don't know how to do this.

Appreicate any assistance

**humdingaling** · 08-12-2013, 02:27 AM

set lrow outside of the loop
you dont have to constantly reassign it

increment lrow before loop ends

Set Master = ThisWorkbook
    lrow = Master.Worksheets(3).Range("A" & Rows.Count).End(xlUp).Row

Do

    Workbooks.Open (myPath & "\" & CurrentFileName)
    Set sourceBook = Workbooks(CurrentFileName)
    Set sourceData = sourceBook.Worksheets("Form Template")
    
        With sourceData
            .Range("D16").Copy
            Master.Worksheets(3).Range("A" & lrow + 1).PasteSpecial (xlPasteValues), Transpose:=True
        End With
        
        With sourceData
            .Range("D10").Copy
            Master.Worksheets(3).Range("B" & lrow + 1).PasteSpecial (xlPasteValues), Transpose:=True
        End With
        
        With sourceData
            .Range("D13").Copy
            Master.Worksheets(3).Range("C" & lrow + 1).PasteSpecial (xlPasteValues), Transpose:=True
        End With
    

        sourceBook.Close False
    lrow = lrow + 1

**humdingaling** · 08-12-2013, 02:31 AM

also

Set Master = ThisWorkbook
    lrow = Master.Worksheets(3).Range("A" & Rows.Count).End(xlUp).Row + 1

Do

    Workbooks.Open (myPath & "\" & CurrentFileName)
    Set sourceBook = Workbooks(CurrentFileName)
    Set sourceData = sourceBook.Worksheets("Form Template")
    
        With sourceData
            .Range("D16").Copy
            Master.Worksheets(3).Range("A" & lrow).PasteSpecial (xlPasteValues), Transpose:=True
            .Range("D10").Copy
            Master.Worksheets(3).Range("B" & lrow).PasteSpecial (xlPasteValues), Transpose:=True
            .Range("D13").Copy
            Master.Worksheets(3).Range("C" & lrow).PasteSpecial (xlPasteValues), Transpose:=True
        End With

        sourceBook.Close False
    
    lrow = lrow + 1
    
'Calling DIR w/o argument finds the next .xlsx file within the current directory.
CurrentFileName = Dir()
Loop While CurrentFileName <> ""

you dont need to "with" every section of sourcedata
also you dont need to +1 every lrow line just do it once in the beginning

**Ravenous** · 08-12-2013, 02:59 AM

Legend! Works perfect and much cleaner, thanks for your help.

**humdingaling** · 08-12-2013, 04:33 AM

not a problem thanks for the rep

Form Extractor Issue

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Form Extractor Issue

Re: Form Extractor Issue

Re: Form Extractor Issue

Re: Form Extractor Issue

Re: Form Extractor Issue

Thread Information

Users Browsing this Thread

Similar Threads

Use Excel as Mobile Web Directory Extractor

line item extractor

Desire Versatile Alphanumeric Extractor From Any String

[SOLVED] Best extractor of rows and columns from a PDF to Excel

Strange issue freezing parent form when unloading a child form

Bookmarks

Bookmarks

Posting Permissions