Macro To Copy Info From Sheet To Sheet

  • Thread starter Thread starter JR
  • Start date Start date
J

JR

Hello,
I hope I explain this correctly. I have a xls file with (sheet 1) with all
my names and addresses. I have employees that will handle different
territories so I need to copy certain portions of (sheet 1) to other sheets
in the same workbook. What I would like it to do, is (sheet 1) is the main
with master list of addresses. I will name each of the following sheets my
emplyees names. What would be great, is that on the main sheet, column "A"
is where I would enter my employees name for whatever address I want to
attach to their sheet, and it would automatically copy the address row to
their sheet. Automatically would be great, so I do not have to each time run
the macro, once name is entered it it copied. hopefully this is possible.

Thanks for the help,
JR
 
Yes, this is possible. It requires an event macro. However, you have to
provide more detail about the layout of your data in both the master sheet
and the employee sheet for anyone to be able to help you.
Specifically, you need to elaborate on the relative position of the address
to the name on the master sheet and where you want this address to be in
the employee sheet. HTH Otto
 
JR
This sheet macro will do what you want. Note that everything between
"With Sheets(Cells(Target.Row, 1).Value)" and "End With" is on one long row.
Note that there must be a space after the word "Copy".
This macro must be placed in the sheet module of the Master sheet. Here's
how. Right-click on the Master sheet tab, select View Code, and paste this
macro into that module.
Otto
 
It would help if I included the macro. Sorry about that.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column > 1 Then Exit Sub
If Target.Row < 1 Then Exit Sub
If Target = "" Then Exit Sub
With Sheets(Cells(Target.Row, 1).Value)
Range(Cells(Target.Row, 1), Cells(Target.Row, 7)).Copy .Range("A" &
Rows.Count).End(xlUp).Offset(1)
End With
End Sub
Otto
 
Hello Otto.

If I understand correctly you would keep Sheet A as master and whatever
changes you make in Sheet A should reflect in the respective employee's
sheet... Right ? if this is what you want then threre is a very
simple way to do it.

Select your cell in the master list and Copy.

then go to the sheet where you want that information to appear and
instead of pasting it, click on Paste Special. This would take you to
a small box where at the bottom you would find a button Paste Link.
Click that and your information from the Master sheet is copied in the
other sheet. If you make any changes to the master sheet, this would
reflect in the other sheets.

Tell me if this is what you want and whether it works. Good Luck.
 
Hello,

I tried it, but it does not seem to work. I need to be able to copy a single
row at one time, depending on the employees name that is in the first
column. Every other row may have a different employee. I know I will have to
has as many work sheets as employees.

Thanks JR
 
Back
Top