I have two sheets, one with a company name where my team enters into B3 whatever company they are working on that day and another sheet with a pivot table and a list of the companies and information relating to each company. I have tried to create a macro whereby the team can enter the company they are working on, and it returns the information from the pivot table. However, I can only get it to return the information from the original macro I recorded and not update when I change the company in cell B3.
The pivot table does not change, it is what I am searching for in the pivot table that changes ie. cell B3. The "find" function doesn't update when I change cell B3. It still returns "Company X" even though I've changed B3 to "Company Y" for example. I am new to this so any help would be greatly appreciated! Thanks in advance!
Sub PEPs()
'
' PEPs Macro
'
'
Range("B3").Select
Selection.Copy
Sheets("Current and Past PEP").Select
Cells.Find(What:="Company X", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Range("B5:E33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PEP's and Committees").Select
Range("B10").Select
ActiveSheet.Paste
End Sub
The pivot table does not change, it is what I am searching for in the pivot table that changes ie. cell B3. The "find" function doesn't update when I change cell B3. It still returns "Company X" even though I've changed B3 to "Company Y" for example. I am new to this so any help would be greatly appreciated! Thanks in advance!
Sub PEPs()
'
' PEPs Macro
'
'
Range("B3").Select
Selection.Copy
Sheets("Current and Past PEP").Select
Cells.Find(What:="Company X", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Range("B5:E33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PEP's and Committees").Select
Range("B10").Select
ActiveSheet.Paste
End Sub