Macro to find cell address and copy data to that cell

[quietfranklin]

I have to copy cells from a report spreadsheet into a master and where they go is determined by three different identifiers; DOCNBR, STNDDOCNBR and ACRN. So, I used CELL,"address",INDEX,MATCH to find the cell address where the information should go in the master. Then I used this macro to copy the data over


Sub foobar()
Dim srng As Range, dws As Worksheet
Dim k As Long, sccol As Long, socol As Long, lcol As Long, dccol As Long, docol As Long
Set srng = ActiveCell.CurrentRegion
Set dws = Windows("Master Unshipped Expired.xlsx").ActiveSheet
With Application.WorksheetFunction
sccol = .Match("REMARKS", srng.Rows(1), 0)
lcol = .Match("Location", srng.Rows(1), 0)
dccol = .Match("April 2017 Remarks", dws.Rows(1), 0)

End With
For k = 2 To srng.Columns.Count
If Not IsEmpty(srng.Cells(k, lcol).Value) Then




MsgBox Prompt:=">" & srng.Cells(k, lcol).Value & "<"
With dws.Range(srng.Cells(k, lcol).Value).EntireRow
srng.Cells(k, sccol).Copy Destination:=.Cells(1, dccol)

End With
End If
Next k
End Sub




This is what the report looks like

DOCNBR	STNDDOCNBR	ACRN	REMARKS	LOCATION
PKUA434	N0038314GM801	AA	Shipped	Z252
PMFF453	N0001917WX001	AB	Unbilled	Z809
PKUAE45	N0042115G0003	BC	No shipping	Z25

<tbody>
</tbody>

And this is what the master looks like

DOCNBR	STNDDOCNBR	ACRN	April 2017 Remarks
PKUA434	N0038314GM801	AA
PMFF453	N0001917WX001	AB
PKUAE45	N0042115G0003	BC

<tbody>
</tbody>

Is there any way I could trim the fat and just make the macro find where the data needs to go?