Deletion of cells by next blank cell command??
 

Hi,

I am using Excel '97.

I have a macro that copies data from an input sheet and pastes it into
another, but it is deleting cells and moving the deleted selection (25 Cells)
to the left on the last paste.

The macro has been written in a setup sheet, which the user then modifies by
input to reflect their needs. The macro works fine in the setup sheet, but
when run in a sequence of macros creating the user defined sheet, it causes
25 cells to be deleted to the left (in the KEY sheet, column E). Please can
anyone tell me why this is happening from the code below?

Sub KEY_CREATE

Dim CELL As Range, RNG As Range
With Worksheets("SETUP")
Set RNG = .Range(.Range("C3:C52"), .Range("C3:C52") .End(xlDown))
End With

For Each CELL in RNG
If CELL < "" Then
iNextFree = Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("SETUP").Range("D" & Cell.Row, "AB" & CELL.Row).Copy
Sheets("KEY").Select
Cells(iNextFree, "E").PasteSpecial Paste:=xlValues,
Operation:=xlNone, _
SkipBlanks:=False, TRANSPOSE:=True
End If
Next
End Sub

Deletion of cells by next blank cell command??
 

I have moved this code nearer to the end of the programme, without adjusting
it in any way, and it now works without deleting any cells. I would still
appreciate any input if this was an obvious solution to an obvious problem?

Thanks.

"ewan7279" wrote:

Hi,

I am using Excel '97.

I have a macro that copies data from an input sheet and pastes it into
another, but it is deleting cells and moving the deleted selection (25 Cells)
to the left on the last paste.

The macro has been written in a setup sheet, which the user then modifies by
input to reflect their needs. The macro works fine in the setup sheet, but
when run in a sequence of macros creating the user defined sheet, it causes
25 cells to be deleted to the left (in the KEY sheet, column E). Please can
anyone tell me why this is happening from the code below?

Sub KEY_CREATE

Dim CELL As Range, RNG As Range
With Worksheets("SETUP")
Set RNG = .Range(.Range("C3:C52"), .Range("C3:C52") .End(xlDown))
End With

For Each CELL in RNG
If CELL < "" Then
iNextFree = Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("SETUP").Range("D" & Cell.Row, "AB" & CELL.Row).Copy
Sheets("KEY").Select
Cells(iNextFree, "E").PasteSpecial Paste:=xlValues,
Operation:=xlNone, _
SkipBlanks:=False, TRANSPOSE:=True
End If
Next
End Sub