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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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")
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,711
Messages
5,512,981
Members
408,928
Latest member
Black Vinyl

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top