Help I need a hero! Advanced Macro open close copy paste etc.

bemcbride

New Member
Joined
May 21, 2012
Messages
47
I have a master commission sheet that has every salesperson with the invoices they get commissioned on. Each individual also has their own individual spreadsheet that has their information along with some personal targets and other information. In the master commission sheet, I have the individual sheets for each person listed.

I want to write a macro that opens this spreadsheet and copy and pastes (values only) onto the specific tab of the individual sheets. After that it should save and close the individual sheet.

Does anyone know if or how something like that could work or be done? I was thinking I could put the file name location in the Workbook column and that might help the macro...I don't know.

Thank you all for any help or assistance you can provide or suggestions. Yall are awesome.
 

Attachments

  • Screenshot 2022-04-11 112140.png
    Screenshot 2022-04-11 112140.png
    13.4 KB · Views: 6

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,151
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Could you upload a couple more individual files so I can test the macro with more than one file?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

bemcbride

New Member
Joined
May 21, 2012
Messages
47
Thank you this works! Can you explain to me key how it works? I f i need to delete a column or add more columns how can i adjust that?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,151
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Currently, the column with the file paths is column Q which is the thirteenth column to the right of column E. If you add or delete columns, you have to change the number "13" in the code (two occurrences) to match the position of the file path column relative to column E.
 

bemcbride

New Member
Joined
May 21, 2012
Messages
47
Currently, the column with the file paths is column Q which is the thirteenth column to the right of column E. If you add or delete columns, you have to change the number "13" in the code (two occurrences) to match the position of the file path column relative to column
 

bemcbride

New Member
Joined
May 21, 2012
Messages
47
That makes sense thank you. I notice when I run the code the individual files no longer recognize the data as being apart of a table so the pivots that work off of it no longer have a reference. Right now I'm going back into the individual files and getting that data we copied over and renaming it as Table1 on all of them. Is there way that when it copies and pastes over it leaves the Table1 name with the copied data?
 

Forum statistics

Threads
1,176,089
Messages
5,901,335
Members
434,886
Latest member
qazibelal

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