So you're overwriting the Index column A with the data from Raw Data?

I think that this works ok:

Option Explicit
'Yes there are two sheets in two separate books. The value to look up
'is in workbook "Audit" on worksheet "Index" in range "A1". What I need
'to do is take that value...go to book "Test" sheet "Raw Data" and look
'in Row 1 across all the headers. There are 30 or so from A1 to AC1.
'Once the match to the look up value is found on sheet "Raw Data" I need
'to copy the entire column including the header and paste it to sheet
'"Index" in column A. I came across another problem. Workbook "Test" is
'generated by a financial system which serializes the file name by adding
'a date and time to the end of the file name.
'Ex. "Test 050922-123034". So I need to activate the book by the first
'four characters and 13 space holders for the date-time. Any suggestions
'are appreciated.

Option Explicit
Sub testme()

Dim RawDataWks As Worksheet
Dim IndexWks As Worksheet
Dim RawDataWkbk As Workbook
Dim wkbk As Workbook
Dim Res As Variant

Set IndexWks = Workbooks("audit.xls").Worksheets("Index")

Set RawDataWkbk = Nothing
For Each wkbk In Application.Workbooks
If LCase(wkbk.Name) Like "test ????????.xls" Then
Set RawDataWkbk = wkbk
Exit For
End If
Next wkbk

If RawDataWkbk Is Nothing Then
MsgBox "I don't see a workbook named ""Test ....xls"" Open!"
Exit Sub
End If

Set RawDataWks = RawDataWkbk.Worksheets("Raw Data")

Res = Application.Match(IndexWks.Range("a1").Value, RawDataWks.Rows(1), 0)

If IsError(Res) Then
MsgBox "No match found for: " & IndexWks.Range("a1").Value
Exit Sub
End If

RawDataWks.Columns(Res).Copy _
Destination:=IndexWks.Range("a1")

End Sub

I didn't do any checks to verify that the worksheet names are found! And it
assumes that Audit.xls is open, too.

mjack003 wrote:

Dave,

Yes there are two sheets in two separate books. The value to look up
is in workbook "Audit" on worksheet "Index" in range "A1". What I need
to do is take that value...go to book "Test" sheet "Raw Data" and look
in Row 1 across all the headers. There are 30 or so from A1 to AC1.
Once the match to the look up value is found on sheet "Raw Data" I need
to copy the entire column including the header and paste it to sheet
"Index" in column A. I came across another problem. Workbook "Test" is
generated by a financial system which serializes the file name by adding
a date and time to the end of the file name.
Ex. "Test 050922-123034". So I need to activate the book by the first
four characters and 13 space holders for the date-time. Any suggestions
are appreciated.

Best Regards,
Mjack

--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=469775

--

Dave Peterson