Data integrity breakdown with VBA macro adding sheets?

[PeterPh]

Hello All,

I have a VBA macro that takes subsequent data sets, runs each through an algorithm, takes a screen print of the result and pastes to each newly created sheet.

The issue I’m trying to get my head around is that after Sheet197 is created as it should be, Sheet198 is a repeat of the image on Sheet197, and then all subsequent sheets have images that are displaced by 1. This exact situation occurs on all of my PCs and laptop, regardless of the amount of RAM and processor power each have.

This issue obviously has nothing to do with the hardware and the VBA code seems solid, so it appears to be a quirk of Excel. Has anybody else come across this type of issue before and if so, can you shed some light on it?

Thanks very much,

Peter.

 

[JLGWhiz]

I think I would try to make an in depth analysis of what the code does on iteration 197 and 198. One way to do that is to put a counter in the loop and use the stop command to halt the macro on iteration 186. Then you can step through one line at a time for iteration 197 and 198 to see exactly what happens on those iterations. You might be surprised.

Example:
Dim counter As Long
counter = 0
For i = 1 To 200 'Start loop
Set sh = Sheets.Add 'Create new sheet
sh.Name = New & i
'Do stuff here
counter = Counter + 1
If counter = 196 The Stop 'Suspend code execution on iteration 196
Next
Example:

Iteration 197 will most likely be routine, except for the lines after copy, or iteration 198 in the lines before copy. Not having the code to look at, I can only surmise that something takes place based on a value some where outside the code in the loop. But it should show up as you step through the code. Could be event code being triggered. Then again, you might find nothing, but it don't hurt to try.