VBA to match 3 data points and move data to another worksheet

[alexx579]

I'm trying to automate a spreadsheet we use at work to capture hours worked on certain projects. I've got it 80% of the way there except for some code I need to write.

On the top of my main sheet, I have a dropdown with 3 employee names to select from.
I also have a dropdown for "Segment". I have 6 segments in the dropdown, each pertain to a roughly 2 week period.

In the table below, I have three dropdowns for the Project name. This is in case someone works on more than one project for the "Segment". The dates in rows 3/4 change dynamically from the chosen "Segment".

I put in some dummy data in the hours worked as you see.

I want some VBA that can match the employee name, to the projects chosen and post the number of ours inputted into that employee's worksheet.

In this pic, I have "Alex Waldorama" having worked some hours in Segment 5 (Dec 1 to Dec 16 - which is dynamically updated based on segment chosen).

"Alex Waldorama" has a separate worksheet with Project 1 to Project 10 as columns, and every day date for Oct 1, 2022 to Sept 30, 2023. I want this code to post hours from pic 1 into the separate spreadsheet. So it should be able to match the employee name to the Worksheet name, and the intersect Month & Day to Project and input these hours into that worksheet.

I hope this makes sense, as I'm not nearly as smart as all of you answering these questions!!! Thanks for all you do to help saps like me make it in life!!!

One last thing, say someone goes back in and puts hours in again and posts... it should be able override what they put in before.

I'm gonna place a square with the VBA so they can just click the Shape (macro will run) and the hours will post. Thanks

 

[DanteAmor]

In your example you have 3 times "December 02", I guess it's a mistake.

Try this:

Sub MoveData()
  Dim shT As Worksheet, sh2 As Worksheet
  Dim i As Long, col As Long, lc As Long
  Dim emply As String
  Dim f As Range
 
  Set shT = Sheets("Time Input")
 
  'Validations
  emply = shT.Range("B1").Value
  If emply = "" Or Not Evaluate("ISREF('" & emply & "'!A1)") Then
    MsgBox "Invalid employee"
    Exit Sub
  End If
 
  lc = shT.Cells(3, Columns.Count).End(1).Column
  Set sh2 = Sheets(emply)
  For i = 5 To 7
    Set f = sh2.Range("1:1").Find(shT.Range("A" & i).Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      col = f.Column
      Set f = sh2.Range("A:A").Find(shT.Range("B3").Value, , xlFormulas, xlWhole, , , False)
      If Not f Is Nothing Then
        sh2.Cells(f.Row, col).Resize(lc).Value = Application.Transpose(shT.Range("B" & i).Resize(1, lc).Value)
      End If
    End If
  Next
End Sub

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Ex:

Dante Amor
	A	B	C	D	E	F	G	H	I
1	Employee	Alex Waldorama		Segment	Seg 5
2
3	Dates	December 01	December 02	December 03	December 04	December 05	December 06	December 07	December 08
4	Project
5	Project 1	2		4				5
6	Project 6			5		6	8		8
7	Project 10		4		4				5
8	Totals	2	4	9	4	6	8	5	13
Time Input

 

[alexx579]

In your example you have 3 times "December 02", I guess it's a mistake.

Try this:

Sub MoveData()
  Dim shT As Worksheet, sh2 As Worksheet
  Dim i As Long, col As Long, lc As Long
  Dim emply As String
  Dim f As Range
 
  Set shT = Sheets("Time Input")
 
  'Validations
  emply = shT.Range("B1").Value
  If emply = "" Or Not Evaluate("ISREF('" & emply & "'!A1)") Then
    MsgBox "Invalid employee"
    Exit Sub
  End If
 
  lc = shT.Cells(3, Columns.Count).End(1).Column
  Set sh2 = Sheets(emply)
  For i = 5 To 7
    Set f = sh2.Range("1:1").Find(shT.Range("A" & i).Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      col = f.Column
      Set f = sh2.Range("A:A").Find(shT.Range("B3").Value, , xlFormulas, xlWhole, , , False)
      If Not f Is Nothing Then
        sh2.Cells(f.Row, col).Resize(lc).Value = Application.Transpose(shT.Range("B" & i).Resize(1, lc).Value)
      End If
    End If
  Next
End Sub

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

@DanteA, yes it was a mistake. I thought I fixed it before I posted... and thanks for the suggestion on XL2BB... I was trying to attach the actual report but didn't see the option. Thank you so much for replying!!~

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.