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

alexx579

New Member
Joined
Aug 19, 2014
Messages
44
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 :):):)
 

Attachments

  • Pic 1.png
    Pic 1.png
    129.9 KB · Views: 21
  • Pic 2.png
    Pic 2.png
    116.7 KB · Views: 21

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
In your example you have 3 times "December 02", I guess it's a mistake.

Try this:

VBA Code:
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
ABCDEFGHI
1EmployeeAlex WaldoramaSegmentSeg 5
2
3DatesDecember 01December 02December 03December 04December 05December 06December 07December 08
4Project
5Project 1245
6Project 65688
7Project 10445
8Totals249468513
Time Input
 
Upvote 0
Solution
In your example you have 3 times "December 02", I guess it's a mistake.

Try this:

VBA Code:
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!!~
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top