So i basically want to get PO matched from the file browse from computer and copied in a way that wherever PO "9207116092" in C4 of file POD is mentioned in Masterfile ColumG "PO" (like we dont have to right "9207116092" again and again just onetime is enough and it paste the POD to all the rows having a PO "9207116092", it should paste the POD date (3rd Jan for this case ) there which is mentioned in D in file POD and ColumnF in Masterfile
NOTE: use the macro Upload POD to upload from the file "POD"
Code
Sub uploadPODdata()
Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long, lastRow As Long, fnd As Range, PO As Range
Set desWB = ThisWorkbook
Set WSdest = desWB.Sheets(1)
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen = False Then Exit Sub
Set OpenBook = Application.Workbooks.Open(FileToOpen)
With Sheets(1)
cRow = .Cells(Rows.Count, "C").End(xlUp).row
For Each PO In Range("C5:C" & cRow)
Set fnd = WSdest.Range("G:G").Find(PO, LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
fnd.Offset(, -1) = PO.Offset(, 1)
End If
Next PO
End With
With WSdest
cRow = .Cells(.Rows.Count, "A").End(xlUp).row
.Range("N4:N" & cRow).Formula = "=if(F4=E4,M4*1,M4*0)"
End With
ActiveWorkbook.Close False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Display More