VBA: 1 extra row copied to new worksheet

[PB7]

VBA team:

I have VBA code which works great, extracting a subset of rows from one sheet, and pasting to another sheet called Zero. Except for 1 glitch, which I've seen previous discussions for, but don't understand:

THE CODE COPIES 1 EXTRA ROW AT THE VERY TOP OF THE NEW WORKSHEET! THE 1 EXTRA ROW IS THE VERY LAST ROW, OF THE CONTIGUOUS NEW SET OF ROWS THAT IT'S COPYING TO THE NEW SHEET.

Here is the current code module, if this helps:

If SheetExists("Zero") = False Then
Sheets.Add 'After:=Sheets("Sheet3")
ActiveSheet.Name = "Zero"
Sheets("Sheet1").Select
Range("A1:Z4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Zero").Select
Range("A1").Select
ActiveSheet.paste
Range("A5").Select
End If
Sheets("Sheet1").Select
nlines = ActiveSheet.Range("A5").End(xlDown).Row
'Start to Scan
For i = 5 To nlines
Range("L" & i).Select
'Based on the comment if need to add to the match sheet then is copied and mark as already send to Zero
If Range("L" & i).Value = "Add to Matches" Then
'take the Row from A to Z
Range("A" & i & ":Z" & i).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Zero").Select
If Range("G5").Value <> "" Then
arch_lines = ActiveSheet.Range("G4").End(xlDown).Row
Range("A" & arch_lines + 1).Select
End If
Selection.PasteSpecial paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("L" & i).Value = "Copied to Zeros"
End If
Next i
Sheets("Zero").Select
Cells.Select
Cells.EntireColumn.AutoFit
End Sub


Realize a lot of code here, for a small glitch. But any comments would be appreciated, thanks.

 

[Jonmo1]

I would suggest stepping through the code...

Adjust your screens so you can see both the workbok AND the VBA window at the same time.
Put your cursor anywhere within your macro
Press F8
This begins the macro.
Continue pressing F8, and it steps through the code 1 line at a time.
And you can observe the results of each step as it happens.

I imagine you'll find the problem

 

[PB7]

Thanks Jonmo. There is a lot of looping and screen re-painting, etc, which causes a lot of moving parts, but will give this a try.

 

[Peter_SSs]

.. any comments would be appreciated, thanks.

When posting code, more people will read it (and therefore possibly help) if it is indented. When it it is all left-aligned it is hard to see where sections start/end etc and therefore much harder to debug. My signature block explains how to use code tags to post indented code.

 

[PB7]

Sure, I understand, my bad. Long day at work, forgot about the indenting. Thanks.