How to change worksheet in a lookup macro?

[Eric Kelcher]

I am trying to change the location of an address look up macro and having trouble. Currently the macro looks at workbook CCSLIST and worksheet CCSList. I want the macro to look at a different worksheet within the same workbook. The worksheet is number 9 and called Contingency.

I did not write the macro just trying to modify to make a little more stream lined and this macro is way past my knowledge base.


Sub RiderDetails(SH As String)
'AUTHOR: A.C. Ward
'CONTACT: [e-mail address removed by admin]
'DATE: 19/7/06
'PURPOSE: Bring in the rider details to the output report
Dim DataFileName As String
Dim DataSH As Worksheet
Dim DataSH_LastRow As Integer

'***** SET THE FILENAME AS REQUIRED *******
DataFileName = "CCSLIST.xls"

Application.ScreenUpdating = False
'open the rider details database
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & DataFileName
Set DataSH = Workbooks(DataFileName).Sheets(1)
With DataSH
DataSH_LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("J1").Formula = "=A1 & "" "" & B1 & "" """
.Range("J1").AutoFill Destination:=.Range("J1:J" & DataSH_LastRow)
End With
'go back to the main file
Workbooks(ThisWorkbook.Name).Activate
'select the output report sheet
Sheets(SH).Select

'insert formulas for first competitor
Range("G11").Formula = "=INDEX('[CCSLIST.xls]CCSLIST'!C:C,MATCH($C11,'[CCSLIST.xls]CCSLIST'!$J:$J,0))"
Range("G11").Copy Destination:=Range("H11:L11")
Range("G11:L11").Copy
'insert formulas for remaining competitors
For Each ce In Range("G12:G" & Cells(Rows.Count, 1).End(xlUp).Row)
'there is an entry in column A. This is to beat the total line splits
If Not IsEmpty(ce.Offset(0, -6)) Then
ce.PasteSpecial (xlPasteFormulas)
End If
Next ce
'convert the formulas to values
Range("G11:L" & Cells(Rows.Count, 1).End(xlUp).Row).Value = Range("G11:L" & Cells(Rows.Count, 1).End(xlUp).Row).Value
'close the database
Workbooks(DataFileName).Close savechanges:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[Damon Ostrander]

Hi Eric,

The only change that I can see that needs to be made is to change

Range("G11").Formula = "=INDEX('[CCSLIST.xls]CCSLIST'!C:C,MATCH($C11,'[CCSLIST.xls]CCSLIST'!$J:$J,0))"

to

Range("G11").Formula = "=INDEX('[CCSLIST.xls]Contingency'!C:C,MATCH($C11,'[CCSLIST.xls]Contingency'!$J:$J,0))"

Damon

 

[Eric Kelcher]

That is what I thought as well and had tried the other thing I thought was the sheet number.


Set DataSH = Workbooks(DataFileName).Sheets(1)

to

Set DataSH = Workbooks(DataFileName).Sheets(8)

(I goofed earlier the correct sheet is number 8)


and either/or both changes does not make it work.

Excel has had me for the last week.