Macro for Copying Data

[dejongt]

Hello,

Looking for help creating a Macro to do the following:

Sheet 1: Employee Info
Sheet 2: Payment Prep
In Sheet 1 we input the employee information and assign theman Employee Info Index Number

I am looking for a Macro that will copy the data from theEmployee Info tab (which ranges from A2 to U2, B2 to U2, C2 to U2 etc.) to thePayment Prep worksheet when the applicable Employee Info Index Number isselected.
So for example if the following info is inputted in theEmployee Info sheet.
Employee Info Index #1 : John Smith

Employee Info Index #2 : Jane Doe
If I input Employee Info Index #1 on the Payment Prepworksheet all of the data from A2 to U2 on the Employee Info tab (relating to John Smith) will be copiedto the Payment Prep worksheet.

Any help is appreciated. Thanks

 

[Yongle]

Re: Help with Macro for Copying Data

welcome to the forum

place code in Payment Prep SHEET module (does not work in standard module)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 1 Then Exit Sub
    On Error Resume Next
    Application.EnableEvents = False
    Sheets("Employee Info").Columns("A").Find(Target).Resize(, 21).Copy Target
    Application.EnableEvents = True
End Sub

When anything is entered in column A in Payment Prep the event macro
- looks for that value in Column A in sheet Employee Info
- and copies the values in columns A:U into sheet Payment Prep
- amend to suit your needs

 

[Yongle]

Re: Help with Macro for Copying Data

I have just looked at OP again
My suggested solution is not what you asked for
I will post a corrected version later

 

[Yongle]

Re: Help with Macro for Copying Data

In Sheet 1 we input the employee information and assign the man Employee Info Index Number

- this sounds like the Index Number is a unique number for each emloyee
- I assumed that is in column A and that is being used for the search

I am looking for a Macro that will copy the data from the Employee Info tab (

which ranges from A2 to U2, B2 to U2, C2 to U2 etc.) to thePayment Prep worksheet when the applicable Employee Info Index Number is selected

Is your data in rows (one for each employee) like this A2:U2, A3:U3, A4:U4 etc

Have a look at my solution (post#2) which is illustrated below & let me know if I misinterpreted what you want (include details)

thnaks

My original solution assumes data like this

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Index No[/td][td]Name1[/td][td]Surname[/td][td]Info_4[/td][td]Info_5[/td][td]Info_6[/td][td]Info_7[/td][td]Info_8[/td][td]Info_9[/td][td]Info_10[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

0001​

[/td][td]John[/td][td]Smith[/td][td]JS_4[/td][td]JS_5[/td][td]JS_6[/td][td]JS_7[/td][td]JS_8[/td][td]JS_9[/td][td]JS_10[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

0002​

[/td][td]Jane[/td][td]Doe[/td][td]JD_4[/td][td]JD_5[/td][td]JD_6[/td][td]JD_7[/td][td]JD_8[/td][td]JD_9[/td][td]JD_10[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

0003​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]etc[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Employee Info[/td][/tr][/table]

and returns this when index number is entered in any cell in column A

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Index No[/td][td]Name1[/td][td]Surname[/td][td]Info_4[/td][td]Info_5[/td][td]Info_6[/td][td]Info_7[/td][td]Info_8[/td][td]Info_9[/td][td]etc ..columns J:U[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

0002​

[/td][td]Jane[/td][td]Doe[/td][td]JD_4[/td][td]JD_5[/td][td]JD_6[/td][td]JD_7[/td][td]JD_8[/td][td]JD_9[/td][td]etc..[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Payment Prep[/td][/tr][/table]

 

[dejongt]

Re: Help with Macro for Copying Data

Your last post is exactly what I am looking for

 

[Yongle]

Re: Help with Macro for Copying Data

Excellent - the code is in post#2