matching and pasting values between 2 sheets

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
349
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
Hi all
i have 2 workbooks

wb1(mi report.xlsx ) and wb2(stafftime.xlsx)

wb1 has 2 columns Column "H" (ID"S) and column "I" (Durations)

wb2 has 2 columns column "E"(ID"S) and column "I" (durations)

want a macro which will pick column H and I of WB1 and open WB2 match the ID'S of column H with list if ID"S in column E of WB2, in front matched ID"S in WB2 , Column I(durations) of wb1 gets pasted in column J of WB2.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
349
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
Dim s As String
Dim i As Integer
s = ""
For i = 1 To 35
If Format(Range("F" & i).Value, "hh:mm") > Format("00:00", "hh:mm") Then
If s > "" Then
s = s & ",F" & i
Else
s = "F" & i
End If
End If
Next
Range(s).Select

Dim cell As Range
Application.ScreenUpdating = False
If ActiveCell.Column <> 6 Then
MsgBox "Please select cells from Column F"
Exit Sub
End If
For Each cell In Selection
With cell
.Offset(, -4).Copy
.Offset(, 2).PasteSpecial xlPasteValues
.Copy
.Offset(, 3).PasteSpecial xlPasteValues
End With
Next cell

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

Columns("H:H").Select
Selection.NumberFormat = "@"










NmStr = ActiveWorkbook.Name
Workbooks.Open ("\\corpfiler14\1698_Normal\K****iJ\staff tst.xlsm")
NmOth = ActiveWorkbook.Name



Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$478").AutoFilter Field:=3, Operator:= _
xlFilterValues, Criteria2:=Array(2, "5/2/2012")
'change date'





Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
Dim ID As Range 'change date'
Set wb1 = Workbooks("MI report") 'Need actual wb name'
Set wb2 = Workbooks("staff tst") 'Need actual wb name
Set sh1 = wb1.Sheets("Sheet1") 'Edit sheet name
Set sh2 = wb2.Sheets("Sheet2") 'Edit sheet name
lr = sh1.Cells(Rows.Count, "H").End(xlUp).Row
Set rng = sh1.Range("H2:H" & lr)
For Each c In rng
Set ID = sh2.Range("E:E").Find(c.Value, LookIn:=xlValues)
If Not ID Is Nothing Then
sh2.Range("I" & ID.Row) = sh1.Range("J" & c.Row)
End If
Next



not working for me properly
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

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
Top