Either there's code you left out -OR- there's no object refs to the
sheets. I suspect you've only provided a snippet and so we can see
what's happening in the missing code!

No need to activate anything as that throws object refs into jeopardy
if you're not syntax correct in your coding. Why are you doing this?

Note that UsedRange.Columns.Count does not reliably return the last
column containing data.

How does shtNew happen?

Why not use an array? Your comparison will get done much faster than
reading/writing each cell...

<aircode
Dim vData1, vData2, vDataOut(), n&, j&
Dim lMaxRows&, lMaxCols&, wksSrc1 As Worksheet, wksSrc2 As Worksheet

Set wksSrc1 = Sheets("Sheet1"): Set wksSrc2 = Sheets("Sheet2")

vData1 = wksSrc1.UsedRange: vData2 = wksSrc2.UsedRange

lMaxRows = Ubound(vData1)
lMaxRows = Iif(UBound(vData2) lMaxRows, _
UBound(Data2), lMaxRows)

lMaxCols = UBound(Data1, 2)
lMaxCols = Iif(UBound(vData2, 2) lMaxCols, _
UBound(vData2, 2), lMaxCols)

'Match the array sizes
vData1 = wksSrc1.UsedRange.Resize(lMaxRows, lMaxCols)
vData2 = wksSrc2.UsedRange.Resize(lMaxRows, lMaxCols)

'Set output array size
Redim vDataOut(1 to lMaxRows, 1 To 3)

Since your loop compares all columns row by row...

For n = 1 To lMaxRows
For j = 1 To lMaxCols
vDataOut(n, 1) = wksSrc1.Cells(n, j).Address
vDataOut(n, 2) = wksSrc1.Cells(n, j).Value
vDataOut(n, 3) = wksSrc1.Cells(n, j).Value
Next 'j
Next 'n

Worksheets.Add After:=wksSrc2
ActiveSheet.Cells(1).Resize(lMaxRows, lMaxCols) = vDataOut

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion