VBA for search and replace in column from pivot table list

Vishneedshelp

New Member
Joined
Dec 20, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello! I have been struggling with finding VBA for the following problem and was wondering if somebody could help me out:

I have a pivot table on Sheet1 where I can select multiple items, ranging from 1-60 vendors. I have another sheet that automatically creates tables based off the number of vendors I've selected - so for example, if I pick 7 vendors in the pivot table on Sheet1, Sheet2 will create 7 tables. Each of these tables has a heading in Column E with a generic name "Vendor_Name".

Is there a VBA to find and replace the first "Vendor_Name" heading in column E on Sheet 2 with the name of the first vendor in the pivot table on Sheet2, then replace the second "Vendor_Name" name heading in column E with the name of the second vendor in the pivot table and so on?

Example:

Pivot Table Heading Sheet1:
Vendor Name 1
Vendor Name 2
Vendor Name 3
Vendor Name 4
Vendor Name 5
Vendor Name 6
Vendor Name 7

Column E headings on Sheet2:
Vendor_Name
Vendor_Name
Vendor_Name
Vendor_Name
Vendor_Name
Vendor_Name
Vendor_Name

Result after macro on Column E on Sheet2:
Vendor Name 1 'Macro replaces "Vendor_Name" with the first name on the Pivot Table in Sheet 1
Vendor Name 2 'Macro replaces "Vendor_Name" with the second name on the Pivot Table in Sheet 1
Vendor Name 3 'Macro replaces "Vendor_Name" with the third name on the Pivot Table in Sheet 1 etc.
Vendor Name 4
Vendor Name 5
Vendor Name 6
Vendor Name 7

I've been trying different find and replace formulas and can't figure this out. Especially since the Pivot Table could be a list of 50 and then column E on sheet2 would then populate with 50 "Vendor_Name" headings in column E.

This is my first time using the forum so if there is any other information I can provide let me know! Thanks!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,127,796
Messages
5,626,929
Members
416,209
Latest member
tan21

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