Any place you have:
Sheets(
add the workbook name:
Workbooks("Firstfile").Sheets(
For the workbook that contains the code, you can use:
Thisworkbook.Sheets(
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
>________________________________
> From: Eugene Bernard <eugene.bern...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Monday, April 28, 2014 11:49 AM
>Subject: Re: $$Excel-Macros$$ Re: find duplicate rows of data between sheets
>macro
>
>
>
>Hi Paul,
>
>
>The code given above works well for the sheets in same workbook.
>send me the code for data in two different work books.
>
>
>Regards
>Eugene
>
>
>
>On Fri, Apr 25, 2014 at 9:15 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>It works perfectly in my workbook.
>>Row 6 is unhighlighted in sheet1
>>and 6 and 13 are unhighlighted in sheet2.
>>
>>better check to make sure the macro was copied correctly,
>>here it is from my workbook:
>>
>>Option Explicit
>>Public Dict_Data1, Dict_Data2
>>Sub Compare()
>> Dim Sht1, Sht2
>> Dim nRows, r, sData, rArray, inx
>> Dim tstart, tstop, TElapsed, TMin, TSec, msg
>>
>> '---------------------------------------------------------
>> Application.ScreenUpdating =
False
>> '---------------------------------------------------------
>> tstart = Timer
>> Set Dict_Data1 = CreateObject("Scripting.Dictionary")
>> Set Dict_Data2 = CreateObject("Scripting.Dictionary")
>> Dict_Data1.RemoveAll
>> Dict_Data2.RemoveAll
>> '---------------------------------------------------------
>> 'Identify sheets to compare
>> '---------------------------------------------------------
>> Sht1 = "Sheet1"
>> Sht2 = "Sheet2"
>> '---------------------------------------------------------
>> ' Clear Highlighting
>> '---------------------------------------------------------
>> Sheets(Sht1).Select
>>
With Cells.Interior
>> .Pattern = xlNone
>> .TintAndShade = 0
>> .PatternTintAndShade = 0
>> End With
>> Sheets(Sht2).Select
>> With Cells.Interior
>> .Pattern = xlNone
>> .TintAndShade = 0
>> .PatternTintAndShade = 0
>> End With
>> '---------------------------------------------------------
>> ' Load data from Sht1 into Dictionary Object
>> '---------------------------------------------------------
>> nRows = Application.WorksheetFunction.CountA(Sheets(Sht1).Range("A1:A" &
>>Sheets(Sht1).Rows.Count))
>> For r = 2 To
nRows
>> If (r Mod 1000 = 0) Then Application.StatusBar = "Loading " & Sht1 &
>>" row " & r & " of " & nRows
>> sData = Trim(Sheets(Sht1).Cells(r, "A").Value)
>> sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "B").Value)
>> sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "C").Value)
>> sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "D").Value)
>> sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "F").Value)
>> sData = sData & "|" & Trim(Sheets(Sht1).Cells(r, "H").Value)
>> sData = sData & "|" & Trim(Sheets(Sht1).Cells(r,
"I").Value)
>> If (Not Dict_Data1.exists(sData)) Then
>> Dict_Data1.Add sData, r
>> '--------------------------------------------------------------
>> Else ' In case of duplicate rows, collect all row numbers
>> '--------------------------------------------------------------
>> Dict_Data1.Item(sData) = Dict_Data1.Item(sData) & "," & r
>> End If
>> Next
>> '---------------------------------------------------------
>> ' Process Sht2 data, compare to sht1
>>
'---------------------------------------------------------
>> nRows = Application.WorksheetFunction.CountA(Sheets(Sht2).Range("A1:A" &
>>Sheets(Sht2).Rows.Count))
>> For r = 2 To nRows
>> If (r Mod 1000 = 0) Then Application.StatusBar = "Processing " & Sht2
>>& " row " & r & " of " & nRows
>> sData = Trim(Sheets(Sht2).Cells(r, "A").Value)
>> sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "B").Value)
>> sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "C").Value)
>> sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "D").Value)
>> sData = sData & "|" & Trim(Sheets(Sht2).Cells(r,
"F").Value)
>> sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "H").Value)
>> sData = sData & "|" & Trim(Sheets(Sht2).Cells(r, "I").Value)
>> If (Dict_Data1.exists(sData)) Then
>> '-----------------------------------
>> 'highlight row in sht2
>> '-----------------------------------
>> Sheets(Sht2).Select
>> With Sheets(Sht2).Range(Cells(r, "A"), Cells(r, "I")).Interior
>> .Pattern =
xlSolid
>> .PatternColorIndex = xlAutomatic
>> .Color = 65535
>> .TintAndShade = 0
>> .PatternTintAndShade = 0
>> End With
>> '-----------------------------------
>> 'highlight rows in sht1
>> '-----------------------------------
>>
Sheets(Sht1).Select
>> rArray = Split(Dict_Data1.Item(sData), ",")
>> For inx = 0 To UBound(rArray)
>> With Sheets(Sht1).Range(Cells(rArray(inx), "A"),
>>Cells(rArray(inx), "I")).Interior
>> .Pattern = xlSolid
>> .PatternColorIndex = xlAutomatic
>> .Color = 65535
>> .TintAndShade =
0
>> .PatternTintAndShade = 0
>> End With
>> Next inx
>> End If
>> Next
>> Application.StatusBar = False
>> Application.ScreenUpdating = True
>> '----------------------------------------------------
>> msg = "Finished: "
>> tstop = Timer
>> TElapsed = tstop - tstart
>> TMin = 0
>> TMin = TElapsed \ 60
>> TSec =
TElapsed Mod 60
>> msg = msg & Chr(13) & Chr(13)
>> If (TMin > 0) Then msg = msg & TMin & " mins "
>> msg = msg & TSec & " sec"
>> '----------------------------------------------------
>> MsgBox msg
>>End Sub
>>
>>
>>
>>Paul
>>-----------------------------------------
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>-----------------------------------------
>>
>>
>> From: Missy786 <misbah.ali...@gmail.com>
>>>To: excel-macros@googlegroups.com
>>>Sent: Friday, April 25, 2014 9:08 AM
>>>Subject: $$Excel-Macros$$ Re: find duplicate rows of data between sheets
>>>macro
>>>
>>>
>>>
>>>Hi Paul,
>>>
>>>
>>>Thank you so much for your time and effort into problem. The code is very
>>>well structured, but I am sorry to inform, that code highlighted every row
>>>in sheet1, as the correct output would be highlighting every row in sheet1
>>>besides row 6, as its not the same as sheet2 row6.
>>>
>>>
>>>I am sorry to cause trouble. Is this problem easy to fix?
>>>
>>>
>>>I really appreciate your time and help.
>>>--
>>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>https://www.facebook.com/discussexcel
>>>
>>>FORUM RULES
>>>
>>>1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
>>>not get quick attention or may not be answered.
>>>2) Don't post a question in the thread of another member.
>>>3) Don't post questions regarding breaking or bypassing any security measure.
>>>4) Acknowledge the responses you receive, good or bad.
>>>5) Jobs posting is not allowed.
>>>6) Sharing copyrighted material and their links is not allowed.
>>>
>>>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>>members are not responsible for any loss.
>>>---
>>>You received this message because you are subscribed to the Google Groups
>>>"MS EXCEL AND VBA MACROS" group.
>>>To unsubscribe from this group and stop receiving emails from it, send an
>>>email to excel-macros+unsubscr...@googlegroups.com.
>>>To post to this group, send email to excel-macros@googlegroups.com.
>>>Visit this group at http://groups.google.com/group/excel-macros.
>>>For more options, visit https://groups.google.com/d/optout.
>>>
>>>
>>>
>>--
>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>https://www.facebook.com/discussexcel
>>
>>FORUM RULES
>>
>>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not
>>get quick attention or may not be answered.
>>2) Don't post a question in the thread of another member.
>>3) Don't post questions regarding breaking or bypassing any security measure.
>>4) Acknowledge the responses you receive, good or bad.
>>5) Jobs posting is not allowed.
>>6) Sharing copyrighted material and their links is not allowed.
>>
>>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>members are not responsible for any loss.
>>---
>>You received this message because you are subscribed to the Google Groups "MS
>>EXCEL AND VBA MACROS" group.
>>To unsubscribe from this group and stop receiving emails from it, send an
>>email to excel-macros+unsubscr...@googlegroups.com.
>>To post to this group, send email to excel-macros@googlegroups.com.
>>Visit this group at http://groups.google.com/group/excel-macros.
>>For more options, visit https://groups.google.com/d/optout.
>>
>
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
