Strange cut and paste result between worksheets

[elf27]

Another situation where I'm cutting and pasting between different worksheets.
The problem is that for some reason, the data copied FROM the source sheet
(sheet1) becomes values only on the paste sheet (sh) AND the source sheet.
Doesn't make any sense to me. I've checked all my other procedures and
narrowed it down to this one.
Any ideas?

--------------------------------------------
Public Sub SyncAESheet()
Dim ArtCol As Long
Dim i As Long
Dim FirstRow As Variant
Dim LastRow As Variant
Dim CellVal As Long
Dim RowNum As Long
Dim ArtNum As Long
Dim LookUpRng As Range
Dim sh As Worksheet

Application.ScreenUpdating = False

Set sh = ActiveSheet
ArtCol = 1 'means col A

With sh ' This loop finds the first and last rows
i = 1
Do Until FirstRow <> Empty
If IsNumeric(.Cells(i, "A")) Then
If .Cells(i, "A").Value > 0 Then
FirstRow = i
End If
ElseIf i > 50 Then FirstRow = 1
Else: FirstRow = Empty
End If
i = i + 1
Loop
i = FirstRow
Do Until LastRow <> Empty
If .Cells(i + 1, "A").Value = "" Then
LastRow = i
ElseIf .Cells(i + 2, "A").Value = "" Then
LastRow = i + 1
Else: LastRow = Empty
i = i + 1
End If
Loop
End With

With sh 'This loop does the replacing
Set LookUpRng = Sheet1.Range("A1:V306")
RowNum = 1

For i = FirstRow To LastRow
ArtNum = sh.Cells(i, ArtCol)
.Cells(ArtNum) = IsText
RowNum = Application.Match(ArtNum, Sheet1.Range("A:A"), 0)
If IsError(RowNum) Then
CellVal = "Error"
End If
Sheet1.Rows(RowNum).Copy
sh.Rows(i).PasteSpecial xlValues
Next i
End With

Application.ScreenUpdating = True

End Sub

 

[Mike H]

Hi,

I would make complete sense if you looked at the 4th to last lines of your
code

sh.Rows(i).PasteSpecial xlValues

and that line is saying paste the values. If you change that to this

sh.Rows(i).PasteSpecial Paste:=xlPasteFormulas

it will paste formula

Mike

 

[elf27]

Mike,

Nope. As I mentioned, I want to past values in to the destination worksheet.
That is why it's paste values in the place you pointed out.
I do not know why it's changing this on the source worksheet. That's the
problem, the source worksheet.

I changed it to pastefomulas just to try it out and it did not solve the
problem. For some reason the source sheet still goes to static cells from
formulas.

 

[Dave Peterson]

Sh is set to the activesheet.

What's the name of the activesheet?
What's the name of the sheet with codename Sheet1?

Could it be that they're the same?

 

[elf27]

There are four sheets that become activesheet. They are different names of
people. Ex: "Jones, John"

Sheet 1 is called "Master Worksheet"

So you think that the placement of the sh = activesheet makes it so that it
pastes to both sheet1 and the other sheets?

 

[Dave Peterson]

Nope.

I'm guessing (and I could be wrong) that you're starting on the sheet that gets
pasted.

So it's just doing a copy|paste special|values right on top of itself.

Add a line to make sure that can't happen:

Set sh = ActiveSheet
if sh.name = sheet1.name then
msgbox "Don't start on this sheet"
exit sub
end if

And see what happens.

Maybe even a msgbox to see what's happening while you're debugging:
msgbox sh.name & vblf & sheet1.name

 

[elf27]

Dave,

Thanks! Another macro started this one on sheet1 and that was the problem.