scrolling down

[Madhup Thakur]

While trying to record a macro I need to fulfill the following task, Chose a
sheet go to the last cell move up using the control+UpArrow, then use the
down arrow to go a vacant cell to perform the copy paste operation.A part of
the macro reads like this

Sheets("Sheet3").Select
Application.Goto Reference:="A65536"
Selection.End(xlUp).Select
Range("A3").Select

However the DownArrow gives me a direct cell reference instead of scrolling
like [Range("A3").Select ]causing data paste errors. How do i sort this out
M. Thakur

 

[Dave Peterson]

You don't need to use the .select's:

Dim RngToCopy as range
Dim NextCell as range

with worksheets("sheet3")
set NextCell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

with worksheets("someothersheet")
set rngtocopy = .range("somerangehere")
end with

RngToCopy.copy _
destination:=NextCell

While trying to record a macro I need to fulfill the following task, Chose a
sheet go to the last cell move up using the control+UpArrow, then use the
down arrow to go a vacant cell to perform the copy paste operation.A part of
the macro reads like this

Sheets("Sheet3").Select
Application.Goto Reference:="A65536"
Selection.End(xlUp).Select
Range("A3").Select

However the DownArrow gives me a direct cell reference instead of scrolling
like [Range("A3").Select ]causing data paste errors. How do i sort this out
M. Thakur

 

[Madhup Thakur]

Thanks a lot Dave,

Being simple I had chosen to use the Macro Record facility in XL2003. The
script that came out was the one that I stated. Your help has been very
gratifying.
Regards.
M.