Macro To Copy Info From Sheet To Sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JR - 08 Jun 2005 17:19 GMT

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

Reply to this Message

Otto Moehrbach - 09 Jun 2005 23:46 GMT

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

> Hello,
> I hope I explain this correctly. I have a xls file with (sheet 1) with all
[quoted text clipped - 11 lines]
> Thanks for the help,
> JR

Reply to this Message

JR - 10 Jun 2005 02:26 GMT

Hello,

I have included a link that may better explain what I am trying to
accomplish.

http://65.18.128.108/2.2/catalog/images/ebay/excel.htm

Thanks 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
[quoted text clipped - 17 lines]
>> Thanks for the help,
>> JR

Reply to this Message

Otto Moehrbach - 10 Jun 2005 21:24 GMT

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

> Hello,
>
[quoted text clipped - 26 lines]
>>> Thanks for the help,
>>> JR

Reply to this Message

Otto Moehrbach - 10 Jun 2005 21:33 GMT

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

> JR
> This sheet macro will do what you want. Note that everything between
[quoted text clipped - 34 lines]
>>>> Thanks for the help,
>>>> JR

Reply to this Message

bengaluru - 10 Jun 2005 22:23 GMT

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.

Signature

bengaluru

Reply to this Message

JR - 11 Jun 2005 14:55 GMT

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

> It would help if I included the macro. Sorry about that.
> Private Sub Worksheet_Change(ByVal Target As Range)
[quoted text clipped - 46 lines]
>>>>> Thanks for the help,
>>>>> JR

Reply to this Message