Macros and VB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hentzer - 26 May 2008 14:24 GMT

Hiya

Already submitted a question but think I may have bn looking at the problem
wrong, so gonna try a different way but I'm clueless when it comes to VB,
once I have run a macro I want to edit it so that it goes to the available
row (row header will be there tho) on a specific spreadsheet in stead of a
certain cell or row, can I do this? If so how? Please remember I am a
novice at VB so make it simple to follow please ;oD LOL

Thanks

Angi

Reply to this Message

Gary''s Student - 26 May 2008 14:53 GMT

If I have not understood you question, then ignore this response. Let's say
we have rows of data on Sheet 17 down to row #13 or any arbitrary row. We
want to exit the macro with the selectied cell at the next available empty
row, say in column A.

So in our case the selection would be at A14.

Sub hentzer()
Sheets("Sheet 17").Activate
Set r = Intersect(ActiveSheet.UsedRange, Range("A:A"))
Cells(1, 1).Offset(r.Count, 0).Select
End Sub

Signature

Gary''s Student - gsnu200789

> Hiya
>
[quoted text clipped - 8 lines]
>
> Angi

Reply to this Message

Hentzer - 26 May 2008 15:05 GMT

Gary

Thanks for your help, and this is closer than I have bn yet,! I may not
have been very clear so I will try and explain LOL what I want to do is check
a box and this set off a macro that goes to sheet 17, next available row say
15 Column A and pastes the info from sheet 1 into the relevant boxes and (I
know how to do the pasting bit etc) but I dont know how to make it go to the
next row and paste it there. I will have 2 check boxes 1 will go to external
and the other to internal but from one list with sequential numbers, any
ideas? ;oD

Thanks for helping

Angi

> If I have not understood you question, then ignore this response. Let's say
> we have rows of data on Sheet 17 down to row #13 or any arbitrary row. We
[quoted text clipped - 21 lines]
> >
> > Angi

Reply to this Message

JLGWhiz - 26 May 2008 16:16 GMT

This would execute on the click event of Checkbox1. You can modify it to
suit your purposes. It finds the last row on sheet 17 using column A as the
column to measure. If column A is not the appropriate column then change the
1 in the statement:

Cells(Rows.Count, 1)

to a number that represents the best column for finding the last row with
data.
Also the sheet name is assumed, based on GS's code. If that is different,
then change that also. You would also need to specify a valid range to copy
on the active sheet (or whichever sheet you are copying from). With all that
fixed, the code would then copy from the source sheet to the destination
sheet next available row.

Sub Checkbox1_Click()
RangeToCopy = 'Supplied by user
lr17 = Sheets("Sheet17").Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.RangeToCopy.Copy Sheets("Sheet17").Range("A" & lr17 + 1)
End Sub

> Gary
>
[quoted text clipped - 36 lines]
> > >
> > > Angi

Reply to this Message