PDA

View Full Version : complicated Match



jmaocubo
04-13-2010, 08:00 AM
hello everybody

I have a worksheet that has a header in cell A3 "Code" in cell B3 "Description" and in cell C3 "Base Rate".

There is the possibility of creating a code to verify other workbooks open and search the headers and copy the data below the header to my worksheet?
Note that the column match may not be in proper order.

For example in my Worksheet cell A3 "code" on the workbook may be in cell H1 or another column.


Thanks in advance

mdmackillop
04-13-2010, 09:41 AM
Will the headers always be in the same row?
Will it exist in more than one workbook/worksheet?
Does the data always go to the same location?

You need to refine your question before you can get a solution.

jmaocubo
04-13-2010, 10:30 AM
Will the headers always be in the same row?
Will it exist in more than one workbook/worksheet?
Does the data always go to the same location?

You need to refine your question before you can get a solution.

Hi mdmackillop


You are absolutely right ... I apologize for not being more specific. But it is difficult to explain in English.

Will the headers always be in the same row?

Yes
_____________

Will it exist in more than one workbook/worksheet?

No. Only my workbook where i need the data and the second workbook (already open) with the mix data, the second workbook has the standart sheets (sheet1, sheet2 and sheet3 but only the sheet1 has data).

___________________________

Does the data always go to the same location?


Yes the data always go to the same location, the problem is in the second workbook that contains the data i need to import to my workbook.

For example: I always need the data below the "Code" (cell A3 in my workbook) and in the second workbook it's possible that data is in column N or L or any other column.



Thanks again for your help

mdmackillop
04-13-2010, 10:43 AM
Option Explicit
Sub GetData()
Dim wsT As Worksheet 'Target
Dim wbS As Workbook 'Source
Dim c As Range

Set wsT = ActiveSheet
Set wbS = Workbooks("Test1.xls")

With wbS.Sheets(1)
Set c = .Cells.Find("Code")
Set c = Range(c.Offset(1), .Cells(Rows.Count, c.Column).End(xlUp))
c.Copy wsT.Range("A4")
End With
End Sub