PDA

View Full Version : zoom in to the pasted area.



noobie
01-28-2007, 10:58 PM
As in title. If i pasted a range just after the last row, how do i zoom in to the area?

Thanks. :)

JimmyTheHand
01-28-2007, 11:20 PM
Hi Noobie,

Try selecting the paste range.

noobie
01-29-2007, 12:29 AM
what if the pasted area may not be definite for every sheet? Because the last row may differ

JimmyTheHand
01-29-2007, 12:49 AM
When pasting you must refer to the destination range somehow.
e.g.
Range1.Copy Destination:=Range2
This reference (Range2) should be good enough for the select method.
Care to upload your code?

noobie
01-29-2007, 05:44 PM
Thanks for the reply. Attached is the code. :)




Sub copy()
ActiveSheet.Unprotect

Dim lrow As Long

With ActiveSheet

lrow = .Range("H" & Rows.Count).End(xlUp).Row + 1
.Range("A1:N10").copy .Range("A" & lrow)
On Error Resume Next
.Range("A" & lrow + 1 & ":N" & lrow + 14).SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error GoTo 0
End With

MsgBox "Please fill in the necessary information in the yellow filled colour cells."
ActiveSheet.Protect
End Sub

JimmyTheHand
01-30-2007, 02:12 AM
See the recommended options for range Select below. They are REM-ed out, in green font.
Sub copy()
ActiveSheet.Unprotect

Dim lrow As Long

With ActiveSheet

lrow = .Range("H" & Rows.Count).End(xlUp).Row + 1
.Range("A1:N10").copy .Range("A" & lrow)
'choose one of the two lines below
'.Range("A" & lrow).Select
'.Range("A" & lrow).Resize(10, 14).Select
On Error Resume Next
.Range("A" & lrow + 1 & ":N" & lrow + 14).SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error Goto 0
End With

MsgBox "Please fill in the necessary information in the yellow filled colour cells."
ActiveSheet.Protect
End Sub
Also, I'm not sure what the purpose of the code is, but I think
.Range("A" & lrow + 1 & ":N" & lrow + 14).SpecialCells(xlCellTypeConstants, 23).Value = ""
should be replaced by .Range("A" & lrow & ":N" & lrow + 9).SpecialCells(xlCellTypeConstants, 23).Value = ""
It that's correct, then it can be simplified down to
.Range("A" & lrow).Resize(10, 14).SpecialCells(xlCellTypeConstants, 23).Value = ""
If you chose the 2nd of the two optional select lines (shown at the beginning of my post), then the whole code would be
Sub copy()
ActiveSheet.Unprotect

Dim lrow As Long

With ActiveSheet

lrow = .Range("H" & Rows.Count).End(xlUp).Row + 1
.Range("A1:N10").copy .Range("A" & lrow)
.Range("A" & lrow).Resize(10, 14).Select
On Error Resume Next
Selection.SpecialCells(xlCellTypeConstants, 23).Value = ""
On Error Goto 0
End With
MsgBox "Please fill in the necessary information in the yellow filled colour cells."
ActiveSheet.Protect
End Sub