Macro read from a variable (changing) cell

Tdup

New Member
Joined
Jan 10, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
Pivot tables don't update without being refreshed. Add this line before your End Sub:
ActiveWorkbook.RefreshAll
 

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
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

New Member
Joined
Jan 10, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,832
Messages
5,598,358
Members
414,233
Latest member
WolverineNurse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top