R
ryguy7272
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 I’m 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!!
Here’s 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 Documents/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
can’t 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---
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 I’m 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!!
Here’s 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 Documents/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
can’t 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---