macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JB - 22 May 2008 22:02 GMT

Hello
I have created a spreadsheet for filling in details in a section and I want
the user to input the data as and when it's needed. It contains the field
headers to guide the user to fill-in the correct data)
I created a macro to copy the section of the spreadsheet i.e. rows 3 to 10
and paste them below, which would be from 11 onwards. but I want to be able
to run the macro again but paste the section below the last one that was
pasted.
When I run it, it pastes over the first section I pasted when recording the
macro. In other words, how do I do it so that it targets the next empty row
after the last section?.
I hope that makes sense
Excel 2003

Reply to this Message

Dave - 22 May 2008 22:30 GMT

Hi,
If you post a copy of your macro, we could suggest the changes needed.
Regards - Dave.

Reply to this Message

JB - 22 May 2008 23:21 GMT

Hi
It goes as follows:

Sub ReportIncident()
'
' ReportIncident Macro
'
' Keyboard Shortcut: Ctrl+r
'
Rows("4:20").Select
Selection.Copy
Range("A23").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=15
Range("A41").Select
End Sub

So when I do run the next one I want to go from under A41 and so on
Thank you
Jen

> Hi,
> If you post a copy of your macro, we could suggest the changes needed.
> Regards - Dave.

Reply to this Message

AndrewArmstrong - 22 May 2008 22:36 GMT

> Hello
> I have created a spreadsheet for filling in details in a section and I want
[quoted text clipped - 9 lines]
> I hope that makes sense
> Excel 2003

Use something like this in your code, where your data would be in
columns A to Z

dim lnglastrow as long
lnglastrow=Range("a65536:z65536").end(xlup).row

range("a"&lnglastrow).select

Reply to this Message

JB - 26 May 2008 11:56 GMT

Thank you for your help
Sorry but I'm very basic in this. Where do I put it? do I add it to the
macro or redo it?
Ta

>> Hello
>> I have created a spreadsheet for filling in details in a section and I
[quoted text clipped - 23 lines]
>
> range("a"&lnglastrow).select

Reply to this Message

Gord Dibben - 26 May 2008 21:25 GMT

Sub test()
Dim rng1 As Range
Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
Range("3:10").Copy _
Destination:=rng1
End Sub

Or if rows are selected by user.................

Sub test2()
Dim rng1 As Range
Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
Selection.Copy _
Destination:=rng1
End Sub

Gord Dibben MS Excel MVP

>Thank you for your help
>Sorry but I'm very basic in this. Where do I put it? do I add it to the
[quoted text clipped - 28 lines]
>>
>> range("a"&lnglastrow).select

Reply to this Message