Formula/VBA that will auto populate information in Sheet 2 based on what is entered in Sheet 1?

[booleanboolean]

I'll try to break this down as simple as possible!

Employee #	Employee Name	Position	Location	Shift Start Time	Call Out Time
123	John Doe	Worker	Office	4PM	11AM

<tbody>
</tbody>

• This is information that I enter in Sheet 1: I enter in the employee number, it populates the employee's name, position, and location. Shift Start Time and Call Out Time I manually select from a drop-down list while I am talking to the employee who is calling out for their shift.

Employee #:
Employee Name:
Position:
Location:
Shift Start Time:
Call Out Time:

• This is what I have in Sheet 2: Essentially, by entering information in Sheet 1, I want the above to auto-populate based on what I entered in there.

Essentially, I could just have these cells equal the respective slots, but the problem I run into is that this file is a CALL LOG, and I am constantly entering in information every call from an employee I get. I want something that, whichever row I am in Sheet 1, to auto populate that information in Sheet 2 , so on and so forth, so it needs to account for how dynamic this process is.

Afterwards, I would like the information in Sheet 2, to disappear once I move on to the next call I get for an employee with new information I then have to fill out in Sheet 1.

Any help would be GREATLY appreciated! Hopefully there is a way to do this.

 

[My Aswer Is This]

Can I assume on sheet(1) your entering your values in Column A to F
And you want this same data entered on sheet(2) Column A to F
And the first time the data gets entered on sheet(2) row (2) and second time row(3) and so on.

You said :

Afterwards, I would like the information in ​

Sheet 2

, to disappear once I move on to the next call I get for an employee with new information I then have to fill out in ​

Sheet 1.

So your entering the data on Sheet(1) then want it automatically entered on Sheet(2)

And then want it to disappear on Sheet(2)

Are you sure you do not want it cleared on sheet(1)

And what does disappear mean? Do you mean you want the data cleared or hidden?

 

[booleanboolean]

Hi, thanks for getting back to my post.

1) Yes, Sheet(1) Columns A - F (A2, B2, C2, etc is where I am putting in Data since A1 - F1 are my headers)
2) I want this same date entered in on Sheet (2), Rows B2, B3, B4, B5, B6, etc since it's vertical input here.
3) And to clairfy, it is the first time data gets entered in Sheet (1) (A2, B2, C2, and even after that since I am constantly inputting data in these columns)

I am sure I do not want it cleared on Sheet 1 since I still have to keep a log of that data for analysis purposes. And by disappear, I mean for the data to be cleared in Sheet 2. So when I receive another phone call and enter in data in Sheet(1) for a new employee, I would now move on to B4, C4, D4, E4, etc for new information entered, and then would want to populate that info in Sheet (2), Rows B2, B3, B4, B5, B6, etc.) Then another call comes in, I would now move on to B6, C6, D6, etc on Sheet (1) with that data to populate in Sheet (2) in the same rows of B2, B3, B4, B5, B6.

 

[My Aswer Is This]

Try this:
When your ready to copy the data to sheet(2)
Double click on the value in column "A" of sheet(1) and your data on that row will be copied to sheet(2) like you asked for.
Into sheet(2) Range("B2:B7")

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
Cancel = True
Dim ans As Long
ans = Target.Row
Sheets(2).Range("B2:B7").Cells.Clear
Range(Cells(ans, 1), Cells(ans, 6)).Copy
Sheets(2).Range("B2").PasteSpecial xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End If
End Sub

 

[booleanboolean]

Trying this now, thanks.

 

[booleanboolean]

It does work! However, my columns are wrong.

The data on Sheet 2 is actually being entered in this range: B4:B11, so I changed that -- however when I double click on Column B in Sheet1 (I hid Column A), the information is misaligned in Sheet 2.

Any way you could provide some insight to fix this? Or how to match certain information on Sheet1 to align with what is needed in Sheet 2?

 

[My Aswer Is This]

You said in your original post:

Yes, Sheet(1) Columns A - F

How are you entering data in column A if Column A is hidden?

You said:

then would want to populate that info in Sheet (2), Rows B2, B3, B4, B5, B6, ​

There is no ​

"Row B2"​

So I assumed you meant Range("B2")
​

I need more information on what you want.

 

[booleanboolean]

Sorry, I was hoping I could try to manipulate the code myself but let me break it down to you on exactly how it is:

Sheet1 ("Call Log") Information is entered into cells B2:J2. The corresponding headers for this information is as followed: Time Off Date (B2), EE#, Employee Name, Position, Location, Shift Start Time, Call Out Time, Call Out Type, Shortcall (J2)

Sheet 2 ("Sheet2") A3:A11 shows the vertical headers for the information from Sheet1 as follows: Call Out Date (A3), Employee #, Employee Nmae, Scheduled Shift, Position, Location, Call Out Time, Call Out Type, Shortcall (A11.) This is the range B3:B11 (where I want info to populate.)

Essentially, I would like the corresponding information from Sheet1 (B2:J2) to populate to the corresponding range (B3:B11) in Sheet 2. However, they do not really align in Sheet 2 vs the headers in Sheet1.

 

[My Aswer Is This]

Install the code as previously mentioned:
Double click on data in column "B" on sheet(1) when you want this data copied to sheet(2)
See if this does what you want:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 2-16-18 7:15 PM EST
If Target.Column = 2 Then
Cancel = True
ans = Target.Row
Sheets(2).Range("B3:B11").Cells.Clear
Range(Cells(ans, 2), Cells(ans, 10)).Copy
Sheets(2).Range("B3").PasteSpecial xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End If
End Sub

 

[vcoolio]

G'day M.A.I.T.,

Unless I've missed something, I think that the OP has created some more confusion for you in post #8 .

If you look at the Sheet1 headings and the Sheet2 headings(transposed), you'll notice that they differ. Is Call Out Date the same as Time Off date? Where does Scheduled Shift come from? Once transposed, Sheet1 "Position" will become Sheet2 "Scheduled Shift", Sheet1 "Location" will become Sheet2 "Position" etc..

However, they do not really align in Sheet 2 vs the headers in Sheet1.

The OP needs to fully clarify this for you.

Cheerio,
vcoolio.