Macro for copy/paste to different worksheets

bishop7262

Board Regular
Joined
Jan 9, 2010
Messages
87
Don't know if this is possible but wondering if you could tell me if this can be done with a macro.

I have 2 columns, Trainer and Client

I run a filter and sort by Trainer. Right now I highlight the clients associated with that trainer and copy those clients then go to "[Trainer Name]" Worksheet and paste those clients in Column A Row 10. I was wondering if a macro can be built to bring up a pop up menu and allow me to do what I manually do.

So I can pick which trainer either by button which runs the macro and it automatically pasted those clients in the appropriate worksheet.

So far I have 6 trainers that has 6 worksheets that I manually go through and copy and paste clients. I will try to get some image or use boxnet if it is needed to clarify what I would like to have done

thank you for your help in advance
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

bishop7262

Board Regular
Joined
Jan 9, 2010
Messages
87
Maybe this will make more sense. I have a worksheet with a column for trainers and clients. I need to create a macro that will filter by name of trainers and copy all the clients in the Client column and past them in the worksheet of that trainer. Now the clients will always be dynamic. So one month it might be 20 clients and the next might be 45 clients. Right now I do this manually and it is very time consuming. Here is an example of what I would like the macro to do:


Excel Workbook
AB
6TrainerClients
7Greg *HammonsAlyssa *Adams
8Greg *HammonsAlyssa *Adams
9Greg *HammonsAlyssa *Adams
10Greg *HammonsAlyssa *Adams
11Greg *HammonsAlyssa *Adams
12Dan LeeCAROLYN *BERNARDO
13Dan LeeCAROLYN *BERNARDO
14Dan LeeCAROLYN *BERNARDO
15Dan LeeCAROLYN *BERNARDO
16Dan LeeDIANNE *CIARCIA
17Dan LeeELIZABETH *CROWLEY
18Dan LeeJOSEPH *THOMPSON
19Susan HammerJOSEPH *THOMPSON
20Susan HammerJOSEPH *THOMPSON
21Susan HammerJOSEPH *THOMPSON
22Susan HammerJOSEPH *THOMPSON
23Susan HammerJOSEPH *THOMPSON
24Susan HammerJOSEPH *THOMPSON
25Susan HammerJOSEPH *THOMPSON
26Susan HammerKEVIN *ZELLO
27Susan HammerKEVIN *ZELLO
Sort Clients




Excel Workbook
AB
6TrainerClients
7Greg *HammonsAlyssa *Adams
8Greg *HammonsAlyssa *Adams
9Greg *HammonsAlyssa *Adams
10Greg *HammonsAlyssa *Adams
11Greg *HammonsAlyssa *Adams
28Greg *HammonsKEVIN *ZELLO
29Greg *HammonsKEVIN *ZELLO
30Greg *HammonsMARVIN *MARTINEZ
31Greg *HammonsMARVIN *MARTINEZ
32Greg *HammonsMARVIN *MARTINEZ
33Greg *HammonsWILFREDO *PABON
34Greg *HammonsWILFREDO *PABON
35Greg *HammonsWILFREDO *PABON
36Greg *HammonsWILFREDO *PABON
Sort Clients



This is Greg's worksheet that those clients will be pasted


Excel Workbook
A
7Clients that trained previous month:
8July-10
10Alyssa *Adams
11Alyssa *Adams
12Alyssa *Adams
13Alyssa *Adams
14Alyssa *Adams
15KEVIN *ZELLO
16KEVIN *ZELLO
17MARVIN *MARTINEZ
18MARVIN *MARTINEZ
19MARVIN *MARTINEZ
20WILFREDO *PABON
21WILFREDO *PABON
22WILFREDO *PABON
23WILFREDO *PABON
24*
25*
26*
Greg
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
'SHEET1 TO MANY SHEETS
I have a macro that may be "ready to use" for parsing rows of data from one sheet to many sheets named for the same values.

It not only can parse the rows, it can create the sheets if they are missing.

You should edit your name entries to NOT include illegal character for sheet names.
 

bishop7262

Board Regular
Joined
Jan 9, 2010
Messages
87
Thank you. I will try it out. What did you mean by edit name entries to not include illegal character for sheet names?

Thanks again

'SHEET1 TO MANY SHEETS
I have a macro that may be "ready to use" for parsing rows of data from one sheet to many sheets named for the same values.

It not only can parse the rows, it can create the sheets if they are missing.

You should edit your name entries to NOT include illegal character for sheet names.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012

ADVERTISEMENT

Your sample data above include asterisks in the trainer names and client name fields. You cannot create sheet tab with names including asterisks or / \ [ ].
 

bishop7262

Board Regular
Joined
Jan 9, 2010
Messages
87
Do not know what I am doing wrong. When I run the macro it creates new tabs with all the names for clients as the worksheet name along with the trainer.


Excel Workbook
AB
1Sam DavisMike Jones
2Sam DavisBabe Ruth
3Sam DavisScott Sims
4Sam DavisJohn Doe
5Sam DavisDoug Free
6Sam DavisSid Vic
7**
8**
9**
Sheet1


I need it to go to Sam Davis worksheet in a particular column and row like this:


Excel Workbook
A
7Clients that trained previous month:
8July-10
10Babe Ruth
11Scott Sims
12John Doe
13Doug Free
14Sid Vic
15
Sam


So I need to sort it by trainer then copy it to appropriate worksheet. I can create a button for each trainer on the Sort Client worksheet and attach the macro to that if that will make it easier.

thanks
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
What did you edit on the macro in the red sections to control where the copy occurs?

Make sure this points to the correct column of names to evaluate. 1-Col A, 2=Col B, etc.
Rich (BB code):
vCol = 1


Make sure this points to the correct sheet name
Rich (BB code):
   Set ws = Sheets("Data")


Range where titles are across top of data, as string, data MUST 'have titles in this row, edit to suit your titles locale
Rich (BB code):
vTitles = "A1:Z1"


This code copies the entire row into A1 on the target sheets. We can edit to copy only one column of data and target it to row 10:
Rich (BB code):
        ws.Range("A" & Range(vTitles).Resize(1, 1) _
            .Row & ":A" & LR).EntireRow.Copy Sheets(MyArr(Itm) & "").Range("A1")

....changes to

        ws.Range("B" & Range(vTitles).Resize(1, 1) _
            .Row & ":B" & LR).Copy Sheets(MyArr(Itm) & "").Range("A10")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,659
Messages
5,832,979
Members
430,178
Latest member
Kruse737

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