Searching external file for values
 

Hi Folks,

I have a datapull from a propriatary db that we use and I have the ability
to clean up the data, no problem.

i have another workbook which is a report in a fixed format with multiple
sections seperated by rows. such that rows 30 - 81 are part of a section with
the section header contained in cell A29.

I would like to be able to run some code that would search for the header
value in the dp workbook and copy the values from the the cells directly
below the header value until the next header value is reached. The dp
contains the necessary data in columns A and B.

All the header values are fixed in terms of value but may move in terms of
cell placement throughout the year.

Any help in the right direction is greatly appreciated.

Searching external file for values
 

Try this code. May need some modifications.

Sub test()

folder = "c:\temp\"
dbfile = "abc.xls"
Workbooks.Open Filename:=folder & dbfile
Set dbBk = ActiveWorkbook
Set wksht = ThisWorkbook.Sheets("Sheet1")

With wksht
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
RowNumber = 1
Do
If IsEmpty(.Range("A" & RowNumber)) Then
HeaderRow = .Range("A" & RowNumber). _
End(xlDown).Row
Else
HeaderRow = RowNumber
End If

RowNumber = HeaderRow + 1
HeaderName = .Range("A" & HeaderRow)
With dbBk.Sheets("sheet1")
Set c = .Columns("A:A").Find(what:=HeaderName, _
LookIn:=xlValues)
If Not c Is Nothing Then
firstData = c.Row + 1
LastData = c.End(xlDown).Row
Rows(firstData & ":" & LastData).Copy _
Destination:=wksht.Rows(RowNumber)

RowNumber = wksht.Range("A" & RowNumber). _
End(xlDown).Row
End If
End With
Loop While RowNumber <= LastRow
End With
End Sub


"Stephen" wrote:

Hi Folks,

I have a datapull from a propriatary db that we use and I have the ability
to clean up the data, no problem.

i have another workbook which is a report in a fixed format with multiple
sections seperated by rows. such that rows 30 - 81 are part of a section with
the section header contained in cell A29.

I would like to be able to run some code that would search for the header
value in the dp workbook and copy the values from the the cells directly
below the header value until the next header value is reached. The dp
contains the necessary data in columns A and B.

All the header values are fixed in terms of value but may move in terms of
cell placement throughout the year.

Any help in the right direction is greatly appreciated.