Copy Method of Range - Wrong Results

[pczegle]

I'm copying the "History" worksheet, the one reserved for "Track Changes", to a backup worksheet called "History Repository". I use the following simple VBA code:
If wb.Worksheets("History").UsedRange.Count > 1 Then _
wb.Worksheets("History").UsedRange.Copy wb.Worksheets("History Repository").Cells(LastRepositoryRow + 1, 1)

I'm getting the following WRONG results:

1 - The "Range" column, the one that shows the cell (or range) that was changed, always reports 3 rows less in "History Repository". For example, if "History" has "A26" then "History Repository" has "A23". This is consistent throughout.

2 - The "Range" column for various rows shows #REF! for all columns in that row. For example if "History" shows "A31, B31, C31, ...", "History Repository" shows "#REF!, #REF!, #REF! ...". I'm not sure whether there is a pattern here for which rows are impacted. I observed it for the following rows based on the "History" sheet's row numbers in the "Range" column:
31, 37, 43, 45, 46, 47

Has anybody experienced this or have any ideas why this might occur? My code does NOT have any logic that alters what's being copied?

 

[moonfish]

#REF is caused if a cell is deleted. For example, if A1 has the formula =A2/2 and you delete the cell A2, A1 formula will become =#REF/2

This only happens if you delete the cell, not if you only delete its contents.

If it happens to an entire row or column, I would guess that someone deleted an entire row or column that was used for reference.

 

[Norie]

Can we see the rest of the code?

 

[pczegle]

#REF is caused if a cell is deleted. For example, if A1 has the formula =A2/2 and you delete the cell A2, A1 formula will become =#REF/2

This only happens if you delete the cell, not if you only delete its contents.

If it happens to an entire row or column, I would guess that someone deleted an entire row or column that was used for reference.

Thanks for your quicker than lightspeed response.

What confuses me is:
1 - All references in the "History" sheet appear to be actual values, NOT references. So deleting anything should NOT cause an issue.
2 - More than that, because the "History" sheet is reserved, Excel doesn't allow anything to be deleted or changed in the "History" sheet.
3 - I have NO logic that deletes anything in either "History" or "History Repository"

 

[pczegle]

Can we see the rest of the code?

There's not much other relevant code than what I already provided.

 

[pczegle]

Update:
Wrong result #1, where the range was off by 3 rows, disappeared after recycling Excel.
Wrong result #2 persists and has been observed again by breakpointing right after the provided code above executed. However, now the issue is more pervasive than before.

I have a Theory: These issues might be related to creating "History Repository" in the same workbook as "History" while Track Changes is active. Pointers in "History" might be changing while it's being copied to "History Repository". I will try copying to another workbook instead and will update this thread with the results.

 

[Norie]

So that's all the code there is?

If there is more code there might be something in it that doesn't seem to be relevant but actually is.

 

[pczegle]

So that's all the code there is?

If there is more code there might be something in it that doesn't seem to be relevant but actually is.

Thanks for your responses. I've done some tests with breakpoints immediately after the code above executes and verified at least part of the issue as per the update above with my theory. I'll update this thread again with the results of testing the theory ASAP.

 

[pczegle]

My theory is confirmed.

If I first copy "History" into an array and then copy the array into another worksheet called "Junk" in the same workbook there is NO problem. This is because copying "History" into an array does NOT change the workbook so the array becomes a frozen image of "History" before any new changes are introduced by copying it.

However, what I did before, copying "History" straight into another worksheet in the same workbook without first freezing the image of "History", does change the workbook making the "History" sheet a moving target while it tries to record those new changes introduced by copying it.

NOTE: I copied the array to the "Junk" worksheet for testing purposes only. I will later switch to copy the array to the "History Repository" sheet. "History Repository" is a running repository requiring more complicated "append" logic and syntax that I'm still working on. I'm not good with syntax so it might take a while, but the concept should work there too. I will post the finished code when I get it working.