Find Macro

[gmazza76]

Good Afternoon,

I am trying to create a Macro that will automatically populate a spreadsheet with information from another sheet within the same workbook.
The code keeps debugging @ the For Each MySht in MyWB.sheets
This should be looking at cell "D4" (MyManager) and pulling the information in as per the information below.

Sorry but i am stuck and unsure which direction to go in

Sub Start_Of_Macro()
    For Each MySht In MyWB.Sheets
        If MySht.Name = MyManager Then
            MySht.Select
            Exit For
        End If
   
Next MySht
 
Set MyFind = MySht.Columns(5).Find(what:=MyProcessNumber, searchdirection:=xlNext, lookat:=xlWhole)
If Not MyFind Is Nothing Then
    ExtractRow = MyFind.Row
Else
    MsgBox "Cannot find value " & MyProcessNumber
    Exit Sub
End If
Set MyFind = Nothing
DValue = Cells(ExtractRow, 1)
Sheets("Amend Quality").Cells(7, 7) = DValue
DAdvocate = Cells(ExtractRow, 2)
Sheets("Amend Quality").Cells(6, 4) = AdvocateValue 'Advocate Name
DYourName = Cells(ExtractRow, 3)
Sheets("Amend Quality").Cells(8, 4) = AssesorValue 'Assessor
DStatus = Cells(ExtractRow, 6)
Sheets("Amend Quality").Cells(11, 8) = StatusValue 'status
PReason = Cells(ExtractRow, 7)
Sheets("Amend Quality").Cells(13, 4) = ProcessValue 'process reason
PSReason = Cells(ExtractRow, 8)
Sheets("Amend Quality").Cells(13, 9) = ProcessSValue 'process Sub reason
Q1Value = Cells(ExtractRow, 9)
Sheets("Amend Quality").Cells(17, 11) = Q1Value 'Q1

All help appreciated.
Many Thanks
Gavin

 

[VoG]

You haven't assigned a workbook to MyWB.

 

[gmazza76]

Sorry what do you mean?

 

[VoG]

For Each MySht In MyWB.Sheets

What is MyWB - you haven't used a statement like

Set MyWB = WorkBooks("Test.xls")

 

[gmazza76]

Cheers,

I realised just after i messaged you back.

It is erroring on

Set MyFind = MySht.Columns(5).Find(what:=MyProcessNumber, searchdirection:=xlNext, lookat:=xlWhole)

I havent specified "MyprocessNumber" previously as Public in another Module. Do i need to redfine this again in this module?

Many thanks
Gavin

 

[VoG]

You need to define MyprocessNumber (and any other variables in other modules) as Public.

 

[gmazza76]

Morning,

I already have defined "MyprocessNumber" as public in another module.
Do i still have to re do this in the present module?

Cheers

 

[VoG]

Well you need to assign a value to it somewhere.