Cut & Paste using Vlookup

[DeadlyGentleman]

New to VBA

I am trying to cut and paste data that I find using Vlookup into a different sheet

Example: Data is logged in sheet3.Range("A2:B300") with the employee's name in A and the data I need in Column B

I want to search Column A for names found in sheet1.range("A6:A45") the reason for this is that the names do not come in order and may not always be within cell("A16")

Once the name is found I need to Cut the Data 1 column right of the names (Column B) and the paste it into sheet1.Range("A6:A45")

I feel like Vlookup is the best way to find the name and the data, but I struggle with copying the data into the other sheet.

Thank you for your help, any advice is welcomed

 

[6StringJazzer]

You want to cut the name, not copy?

So you want to loop through Sheet1!A6:A45. For each value, look it up on Sheet3, and add the value found in column B to column B in Sheet1.

Why do you need VBA for this? Why not just use a VLOOKUP formula in Sheet1!B6:B45?

 

[DeadlyGentleman]

You want to cut the name, not copy?

So you want to loop through Sheet1!A6:A45. For each value, look it up on Sheet3, and add the value found in column B to column B in Sheet1.

Why do you need VBA for this? Why not just use a VLOOKUP formula in Sheet1!B6:B45?

I have attached pictures of the Source & the Paste Area.

I don't want to cut the name. I want to cut the timestamp and log it into the "Time In" column. That way I can delete empty cells & the name next to the empty cell in order to reduce multiple values, that way when employees clock in and clock out, the VBA isn't having to sort through duplicate names when the employee clocks out/clocks in and so on.

I have more than one function going into this VBA, just trying to find help for the part that is stumping me currently.

 

[6StringJazzer]

Not possible to test without your file and test data

Public Sub CutPasteTimeStamps()

   Dim R As Long ' row number on Sheet3
   Dim Found As Range
   
   R = 2
   With Sheet3
      For R = 2 To .Cells(.Rows.Count, "A").End(xlUp).row
         Set Found = Sheet1.Range("A:A").Find(what:=.Cells(R, "A"), LookIn:=Values, lookat:=xlWhole)
         If Found Is Nothing Then
            MsgBox "Name """ & .Cells(R, "A") & """ not found on Sheet1"
         Else
            Sheet1.Cells(Found.row, "B").Cut
            .Cells(R, "B").PasteSpecial
            Application.CutCopyMode = False
         End If
      Next R
   End With
      
   Loop

End Sub

 

[DeadlyGentleman]

Not possible to test without your file and test data

Public Sub CutPasteTimeStamps()

   Dim R As Long ' row number on Sheet3
   Dim Found As Range
  
   R = 2
   With Sheet3
      For R = 2 To .Cells(.Rows.Count, "A").End(xlUp).row
         Set Found = Sheet1.Range("A:A").Find(what:=.Cells(R, "A"), LookIn:=Values, lookat:=xlWhole)
         If Found Is Nothing Then
            MsgBox "Name """ & .Cells(R, "A") & """ not found on Sheet1"
         Else
            Sheet1.Cells(Found.row, "B").Cut
            .Cells(R, "B").PasteSpecial
            Application.CutCopyMode = False
         End If
      Next R
   End With
     
   Loop

End Sub

Had some help on a different forum and got it solved. Much appreciated for your time & effort.

 

[6StringJazzer]

There is a rule here that requires providing the link if you post the same question on a different forum. As it is I feel as though my time & effort was wasted since I did not know you got it solved somewhere else.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Please provide links to any other sites where you have asked this question.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.