Paste and display issue

[Patrick C. Simonds]

My code below does exactly what I need it to do. My only issue is that if
it is pasting the data way down on the sheet, the user has to scroll down to
find if. Any way to have the newly pasted data display after it is pasted?



Sub Paste_Data()

Dim BCell, NBCell
Dim PasteTo As Range
Dim rng

On Error GoTo Done

Range("B5").Select

Application.ScreenUpdating = False
Application.EnableEvents = False

For i = 1 To 65536
If ActiveCell.Value = Empty Then
BCell = "B" & CStr(i - 1)
NBCell = "B" & CStr(i - 2)

GoTo Finished

Else
Range("B" & CStr(i + 1)).Select
End If
Next i


Finished:

Application.EnableEvents = True

Set rng = Cells(ActiveCell.Row, 1)
rng(1, 2).Select

Selection.PasteSpecial Paste:=xlPasteValues

Application.EnableEvents = True

Done:

End Sub

 

[Doug Glancy]

Patrick,

I know you say your code does exactly what you want, but it contains unused
variables, undeclared variables, and doesn't turn ScreenUpdating back on.
Further, if you have an error you won't turn EnableEvents back on either.

I think what you are doing is pasting data at the next empty cell in column
B. To do this and to eliminate some of the issues above, I took the liberty
of rewriting your code. It also puts the last first pasted cell in the
upper left area of the screen

Sub Paste_Data()
Dim lngLastRow As Long

On Error GoTo Error_handler
Application.ScreenUpdating = False
Application.EnableEvents = False
lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & lngLastRow + 1).PasteSpecial Paste:=xlPasteValues
Application.Goto reference:=Range("A" & lngLastRow), scroll:=True

Error_handler:
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

My code below does exactly what I need it to do. My only issue is that if
it is pasting the data way down on the sheet, the user has to scroll down
to find if. Any way to have the newly pasted data display after it is
pasted?



Sub Paste_Data()

Dim BCell, NBCell
Dim PasteTo As Range
Dim rng

On Error GoTo Done

Range("B5").Select

Application.ScreenUpdating = False
Application.EnableEvents = False

For i = 1 To 65536
If ActiveCell.Value = Empty Then
BCell = "B" & CStr(i - 1)
NBCell = "B" & CStr(i - 2)

GoTo Finished

Else
Range("B" & CStr(i + 1)).Select
End If
Next i


Finished:

Application.EnableEvents = True

Set rng = Cells(ActiveCell.Row, 1)
rng(1, 2).Select

Selection.PasteSpecial Paste:=xlPasteValues

Application.EnableEvents = True

Done:

End Sub

__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4380 (20090829) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4380 (20090829) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Patrick C. Simonds]

Thank you

Patrick,

I know you say your code does exactly what you want, but it contains
unused variables, undeclared variables, and doesn't turn ScreenUpdating
back on. Further, if you have an error you won't turn EnableEvents back on
either.

I think what you are doing is pasting data at the next empty cell in
column B. To do this and to eliminate some of the issues above, I took
the liberty of rewriting your code. It also puts the last first pasted
cell in the upper left area of the screen

Sub Paste_Data()
Dim lngLastRow As Long

On Error GoTo Error_handler
Application.ScreenUpdating = False
Application.EnableEvents = False
lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & lngLastRow + 1).PasteSpecial Paste:=xlPasteValues
Application.Goto reference:=Range("A" & lngLastRow), scroll:=True

Error_handler:
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4380 (20090829) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com