I have this macro that compares values in two columns (A & M) in the same
sheet and if there is a match, it takes the value in the adjacent column (N)
and pastes it into ColumnE.

Sub MatchAandM()
Dim Lrow As Long
Dim RowCount As Long
Dim xRng As Range
Lrow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lrow
FindVal = Range("A" & RowCount)
Set xRng = Columns("M:M").Find(What:=FindVal, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not xRng Is Nothing Then
xRng.Offset(0, 1).Copy xRng.Offset(0, -8)
End If
Next RowCount
End Sub

Works great!! Now, what Im trying to do match numbers in Column E in Sheet
€œSheet1€� ActiveWorkbook with numbers in ColumnA of WorkBook named
€œMarketPrices€� and Sheet named €œMarketPrices€�, and if there is a match, copy
paste the value from ColumnB of €œMarketPrices€� to ColumnE of the €œSheet1€�
ActiveWorkbook. Make sense? To make this just a tad harder, both files are
stored on SharePoint!!

Heres my (non-working) code for moving the data between the two WorkBooks:
Sub MoveData()
Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set DSh = ActiveWorkbook.ActiveSheet
'Set SSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
Set SSh = "https://collaboration.net/sites/Shared%20Documents/Workbooks(" &
Chr(34) & "MarketPrices.xls" & Chr(34) & ").Sheets(" & Chr(34) &
"MarketPrices" & Chr(34) & ").Range(" & Chr(34) & "B2:B5" & Chr(34) & ")"
LastRow = DSh.Cells(Cells.Rows.Count, "K").End(xlUp).Row
SSh.Range("B2:B" & LastRow).Copy
LastRow = SSh.Cells(Cells.Rows.Count, "B").End(xlUp).Row
DSh.Range("K" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

The code fails on this line:
Set SSh =

Run Time error 424
Object required.

I guess VBA is not recognizing the workbook, or sheet, or even range. I
cant figure out the problem with the object not found. Once that is
resolved, I need to use the basic logic form the first macro and incorporate
it into the second macro.

Any ideas?

Thanks so much!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.