Macro read from a variable (changing) cell

[Tdup]

I have two sheets, one with a company name where my team enters 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. 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
Range("B5").Select
Cells.Find(What:="Motus Holdings Limited", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range("B2148:E2162").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PEP's and Committees").Select
Range("B10").Select
ActiveSheet.Paste
End Sub

 

[BobUmlas]

Pivot tables don't update without being refreshed. Add this line before your End Sub:
ActiveWorkbook.RefreshAll

 

[BobUmlas]

On another note, you don't need all the Select statements, especially after you do them all you reselect Range("B2148:E2162") !
Maybe this instead of all you have!:

Sub PEPs()
'
' PEPs Macro

Range("B2148:E2162").Copy
Sheets("PEP's and Committees").Select
Range("B10").Select
ActiveSheet.Paste
Activeworkbook.Refreshall
End Sub

 

[Tdup]

Pivot tables don't update without being refreshed. Add this line before your End Sub:
ActiveWorkbook.RefreshAll

Thanks for the response. I tried this and I don't think my explanation was clear. 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. Thanks again for your help.

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