Next Row

[Hazel]

Hi Everbody
A little help needed with the following I have the following macro on sheet
"List2" how would I add another macro that would pick the next row on sheet
"List1" I would prefer to add a button on "List2" and by just clicking on it
it will go to the next row.

Sub Macro1()
Dim myCell As Range


For Each myCell In Worksheets("LIST1").Range("A2:A2")
If myCell.Value <> "" Then


Worksheets("LIST2").Range("C6").Value = myCell(1, 2).Value
Worksheets("LIST2").Range("C7").Value = myCell(1, 3).Value
Worksheets("LIST2").Range("C8").Value = myCell(1, 4).Value
Worksheets("LIST2").Range("C9").Value = myCell(1, 5).Value
Worksheets("LIST2").Range("C10").Value = myCell(1, 6).Value
Worksheets("LIST2").Range("F8").Value = myCell(1, 7).Value
Worksheets("LIST2").Range("C11").Value = myCell(1, 8).Value
Worksheets("LIST2").Range("F9").Value = myCell(1, 9).Value
Worksheets("LIST2").Range("F10").Value = myCell(1, 10).Value
Worksheets("LIST2").Range("F11").Value = myCell(1, 11).Value
Worksheets("LIST2").Range("F12").Value = myCell(1, 12).Value
Worksheets("LIST2").Range("F13").Value = myCell(1, 13).Value
Worksheets("LIST2").Range("F14").Value = myCell(1, 14).Value
Worksheets("LIST2").Range("C4").Value = myCell(1, 15).Value
Worksheets("LIST2").Range("E3").Value = myCell(1, 16).Value

End If
Next myCell

End Sub


--
Many thanks

hazel

[Trevor Shuttleworth]

Hazel

do you mean something like:

For Each myCell In Worksheets("LIST1").Range("A2:A200")

Your current statement only processes one cell, A2. The above construct
would process all the cells from A2 to A200

Regards

Trevor


"Hazel" <[email protected]> wrote in message
news:[email protected]...
> Hi Everbody
> A little help needed with the following I have the following macro on
> sheet
> "List2" how would I add another macro that would pick the next row on
> sheet
> "List1" I would prefer to add a button on "List2" and by just clicking on
> it
> it will go to the next row.
>
> Sub Macro1()
> Dim myCell As Range
>
>
> For Each myCell In Worksheets("LIST1").Range("A2:A2")
> If myCell.Value <> "" Then
>
>
> Worksheets("LIST2").Range("C6").Value = myCell(1, 2).Value
> Worksheets("LIST2").Range("C7").Value = myCell(1, 3).Value
> Worksheets("LIST2").Range("C8").Value = myCell(1, 4).Value
> Worksheets("LIST2").Range("C9").Value = myCell(1, 5).Value
> Worksheets("LIST2").Range("C10").Value = myCell(1, 6).Value
> Worksheets("LIST2").Range("F8").Value = myCell(1, 7).Value
> Worksheets("LIST2").Range("C11").Value = myCell(1, 8).Value
> Worksheets("LIST2").Range("F9").Value = myCell(1, 9).Value
> Worksheets("LIST2").Range("F10").Value = myCell(1, 10).Value
> Worksheets("LIST2").Range("F11").Value = myCell(1, 11).Value
> Worksheets("LIST2").Range("F12").Value = myCell(1, 12).Value
> Worksheets("LIST2").Range("F13").Value = myCell(1, 13).Value
> Worksheets("LIST2").Range("F14").Value = myCell(1, 14).Value
> Worksheets("LIST2").Range("C4").Value = myCell(1, 15).Value
> Worksheets("LIST2").Range("E3").Value = myCell(1, 16).Value
>
> End If
> Next myCell
>
> End Sub
>
>
> --
> Many thanks
>
> hazel

[Hazel]

Hi Trevor

Thanks for the reply but actually I've tried that and all it does is select
the row at the end of the range e.g row 200 using your reply -- what I want
to do is click the button whilst its showing the data on "LIST2" from row 2
on "LIST1" and on clicking it moves to row 3 "List1" and replaces the data
that is showing in "List2" I can then print that sheet off. Does all that
make sense???
--
Many thanks

hazel


"Trevor Shuttleworth" wrote:

> Hazel
>
> do you mean something like:
>
> For Each myCell In Worksheets("LIST1").Range("A2:A200")
>
> Your current statement only processes one cell, A2. The above construct
> would process all the cells from A2 to A200
>
> Regards
>
> Trevor
>
>
> "Hazel" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Everbody
> > A little help needed with the following I have the following macro on
> > sheet
> > "List2" how would I add another macro that would pick the next row on
> > sheet
> > "List1" I would prefer to add a button on "List2" and by just clicking on
> > it
> > it will go to the next row.
> >
> > Sub Macro1()
> > Dim myCell As Range
> >
> >
> > For Each myCell In Worksheets("LIST1").Range("A2:A2")
> > If myCell.Value <> "" Then
> >
> >
> > Worksheets("LIST2").Range("C6").Value = myCell(1, 2).Value
> > Worksheets("LIST2").Range("C7").Value = myCell(1, 3).Value
> > Worksheets("LIST2").Range("C8").Value = myCell(1, 4).Value
> > Worksheets("LIST2").Range("C9").Value = myCell(1, 5).Value
> > Worksheets("LIST2").Range("C10").Value = myCell(1, 6).Value
> > Worksheets("LIST2").Range("F8").Value = myCell(1, 7).Value
> > Worksheets("LIST2").Range("C11").Value = myCell(1, 8).Value
> > Worksheets("LIST2").Range("F9").Value = myCell(1, 9).Value
> > Worksheets("LIST2").Range("F10").Value = myCell(1, 10).Value
> > Worksheets("LIST2").Range("F11").Value = myCell(1, 11).Value
> > Worksheets("LIST2").Range("F12").Value = myCell(1, 12).Value
> > Worksheets("LIST2").Range("F13").Value = myCell(1, 13).Value
> > Worksheets("LIST2").Range("F14").Value = myCell(1, 14).Value
> > Worksheets("LIST2").Range("C4").Value = myCell(1, 15).Value
> > Worksheets("LIST2").Range("E3").Value = myCell(1, 16).Value
> >
> > End If
> > Next myCell
> >
> > End Sub
> >
> >
> > --
> > Many thanks
> >
> > hazel
>
>
>

[avveerkar]

Hi Everbody
A little help needed with the following I have the following macro on sheet
"List2" how would I add another macro that would pick the next row on sheet
"List1" I would prefer to add a button on "List2" and by just clicking on it
it will go to the next row.

Sub Macro1()
Dim myCell As Range


For Each myCell In Worksheets("LIST1").Range("A2:A2")
If myCell.Value <> "" Then


Worksheets("LIST2").Range("C6").Value = myCell(1, 2).Value
Worksheets("LIST2").Range("C7").Value = myCell(1, 3).Value
Worksheets("LIST2").Range("C8").Value = myCell(1, 4).Value
Worksheets("LIST2").Range("C9").Value = myCell(1, 5).Value
Worksheets("LIST2").Range("C10").Value = myCell(1, 6).Value
Worksheets("LIST2").Range("F8").Value = myCell(1, 7).Value
Worksheets("LIST2").Range("C11").Value = myCell(1, 8).Value
Worksheets("LIST2").Range("F9").Value = myCell(1, 9).Value
Worksheets("LIST2").Range("F10").Value = myCell(1, 10).Value
Worksheets("LIST2").Range("F11").Value = myCell(1, 11).Value
Worksheets("LIST2").Range("F12").Value = myCell(1, 12).Value
Worksheets("LIST2").Range("F13").Value = myCell(1, 13).Value
Worksheets("LIST2").Range("F14").Value = myCell(1, 14).Value
Worksheets("LIST2").Range("C4").Value = myCell(1, 15).Value
Worksheets("LIST2").Range("E3").Value = myCell(1, 16).Value

End If
Next myCell

End Sub


--
Many thanks

hazel

Hazel,

I won't say that what I am suggesting is a very elegant code. But let us do it with minimum changes to your code . You need to declare common variable rowcnt and add one button ( commandbutton2) and have a procedure associated to it. You could also add commandbutton1 and associate your procedure to it. Click cmdbtn2 to go to next row of LIST1 and then click cmdbtn1 to run your macro to fetch the values from next row ( selected by cmdbtn2) and replace on LIST2. Your LIST2 code sheet should look like this.

Dim rowcnt As Integer
_______________________________________________________________

'Sub Macro1() ( I am changing this line of yours to associate to cmdbtn1)

Sub CommandButton1_click()
Dim myCell As Range

For Each myCell In Worksheets("LIST1").Range("A" & (2+rowcnt))
If myCell.Value <> "" Then

Worksheets("LIST2").Range("C6").Value = myCell(1, 2).Value
Worksheets("LIST2").Range("C7").Value = myCell(1, 3).Value
Worksheets("LIST2").Range("C8").Value = myCell(1, 4).Value
Worksheets("LIST2").Range("C9").Value = myCell(1, 5).Value
Worksheets("LIST2").Range("C10").Value = myCell(1, 6).Value
Worksheets("LIST2").Range("F8").Value = myCell(1, 7).Value
Worksheets("LIST2").Range("C11").Value = myCell(1, 8).Value
Worksheets("LIST2").Range("F9").Value = myCell(1, 9).Value
Worksheets("LIST2").Range("F10").Value = myCell(1, 10).Value
Worksheets("LIST2").Range("F11").Value = myCell(1, 11).Value
Worksheets("LIST2").Range("F12").Value = myCell(1, 12).Value
Worksheets("LIST2").Range("F13").Value = myCell(1, 13).Value
Worksheets("LIST2").Range("F14").Value = myCell(1, 14).Value
Worksheets("LIST2").Range("C4").Value = myCell(1, 15).Value
Worksheets("LIST2").Range("E3").Value = myCell(1, 16).Value

End If
Next myCell

End Sub

_______________________________________________________

CommandButton2_click
rowcnt=rowcnt+1
MsgBox " LIST1 Row No. " & (rowcnt+2)
End Sub

The way you are addressing the cells on LIST1 - myCell As A2 and then using myCell(1,2), myCell(1,3) etc -could be some what confusing.You are setting myCell as A2 and then is using offset addressing to A2 . But if you are getting correct results for your first row A2 the way you expect, then we will leave it at that.. Assuming you are getting correct results for first row I have not changed your code at all, except Range("A2:A2") to Range ("A" & (2+rowcnt)).

A V Veerkar

[Trevor Shuttleworth]

Hazel

<<Does all that make sense???>> Mmmm, well, no, sorry.

Can you explain, step by step what you are trying to achieve. Maybe
describe the sheets and the data, start point and end point.

Are you trying to copy a varying range of data from 1 sheet to another ?
Are you saying that, for example, you have (a cell in) row 2 selected on
Sheet 2. You press "the button" and it copies the data from row 2 on Sheet
1 to row 2 on Sheet 2 ? And then what ? You press the button again and it
moves down and does it again ? And so on every time you press the button ?
Or are you saying you want to copy all the rows on Sheet 1 to the same
position on Sheet 2 ?

And in between copying the data, you want to print it ?

OK, maybe I'm getting there now I've "talked it through ...

Is this what you want ?

Sub Macro1()
Dim myCell As Range
For Each myCell In Worksheets("LIST1").Range("A2:A20")
If myCell.Value <> "" Then
With Worksheets("LIST2")
.Range("C6").Value = myCell(1, 2).Value
.Range("C7").Value = myCell(1, 3).Value
.Range("C8").Value = myCell(1, 4).Value
.Range("C9").Value = myCell(1, 5).Value
.Range("C10").Value = myCell(1, 6).Value
.Range("F8").Value = myCell(1, 7).Value
.Range("C11").Value = myCell(1, 8).Value
.Range("F9").Value = myCell(1, 9).Value
.Range("F10").Value = myCell(1, 10).Value
.Range("F11").Value = myCell(1, 11).Value
.Range("F12").Value = myCell(1, 12).Value
.Range("F13").Value = myCell(1, 13).Value
.Range("F14").Value = myCell(1, 14).Value
.Range("C4").Value = myCell(1, 15).Value
.Range("E3").Value = myCell(1, 16).Value
.PrintPreview
End With
End If
Next myCell
End Sub

Change PrintPreview to PrintOut to actually Print each Sheet out. Use
PrintPreview while you experiment.

Regards

Trevor


"Hazel" <[email protected]> wrote in message
news:[email protected]...
> Hi Trevor
>
> Thanks for the reply but actually I've tried that and all it does is
> select
> the row at the end of the range e.g row 200 using your reply -- what I
> want
> to do is click the button whilst its showing the data on "LIST2" from row
> 2
> on "LIST1" and on clicking it moves to row 3 "List1" and replaces the data
> that is showing in "List2" I can then print that sheet off. Does all that
> make sense???
> --
> Many thanks
>
> hazel
>
>
> "Trevor Shuttleworth" wrote:
>
>> Hazel
>>
>> do you mean something like:
>>
>> For Each myCell In Worksheets("LIST1").Range("A2:A200")
>>
>> Your current statement only processes one cell, A2. The above construct
>> would process all the cells from A2 to A200
>>
>> Regards
>>
>> Trevor
>>
>>
>> "Hazel" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Everbody
>> > A little help needed with the following I have the following macro on
>> > sheet
>> > "List2" how would I add another macro that would pick the next row on
>> > sheet
>> > "List1" I would prefer to add a button on "List2" and by just clicking
>> > on
>> > it
>> > it will go to the next row.
>> >
>> > Sub Macro1()
>> > Dim myCell As Range
>> >
>> >
>> > For Each myCell In Worksheets("LIST1").Range("A2:A2")
>> > If myCell.Value <> "" Then
>> >
>> >
>> > Worksheets("LIST2").Range("C6").Value = myCell(1, 2).Value
>> > Worksheets("LIST2").Range("C7").Value = myCell(1, 3).Value
>> > Worksheets("LIST2").Range("C8").Value = myCell(1, 4).Value
>> > Worksheets("LIST2").Range("C9").Value = myCell(1, 5).Value
>> > Worksheets("LIST2").Range("C10").Value = myCell(1, 6).Value
>> > Worksheets("LIST2").Range("F8").Value = myCell(1, 7).Value
>> > Worksheets("LIST2").Range("C11").Value = myCell(1, 8).Value
>> > Worksheets("LIST2").Range("F9").Value = myCell(1, 9).Value
>> > Worksheets("LIST2").Range("F10").Value = myCell(1, 10).Value
>> > Worksheets("LIST2").Range("F11").Value = myCell(1, 11).Value
>> > Worksheets("LIST2").Range("F12").Value = myCell(1, 12).Value
>> > Worksheets("LIST2").Range("F13").Value = myCell(1, 13).Value
>> > Worksheets("LIST2").Range("F14").Value = myCell(1, 14).Value
>> > Worksheets("LIST2").Range("C4").Value = myCell(1, 15).Value
>> > Worksheets("LIST2").Range("E3").Value = myCell(1, 16).Value
>> >
>> > End If
>> > Next myCell
>> >
>> > End Sub
>> >
>> >
>> > --
>> > Many thanks
>> >
>> > hazel
>>
>>
>>

[Hazel]

Hi

With a few minor adjustments got it all working exactly as I wanted -- thank
you both very much
--
Many thanks

hazel


"avveerkar" wrote:

>
> Hazel Wrote:
> > Hi Everbody
> > A little help needed with the following I have the following macro on
> > sheet
> > "List2" how would I add another macro that would pick the next row on
> > sheet
> > "List1" I would prefer to add a button on "List2" and by just clicking
> > on it
> > it will go to the next row.
> >
> > Sub Macro1()
> > Dim myCell As Range
> >
> >
> > For Each myCell In Worksheets("LIST1").Range("A2:A2")
> > If myCell.Value <> "" Then
> >
> >
> > Worksheets("LIST2").Range("C6").Value = myCell(1, 2).Value
> > Worksheets("LIST2").Range("C7").Value = myCell(1, 3).Value
> > Worksheets("LIST2").Range("C8").Value = myCell(1, 4).Value
> > Worksheets("LIST2").Range("C9").Value = myCell(1, 5).Value
> > Worksheets("LIST2").Range("C10").Value = myCell(1, 6).Value
> > Worksheets("LIST2").Range("F8").Value = myCell(1, 7).Value
> > Worksheets("LIST2").Range("C11").Value = myCell(1, 8).Value
> > Worksheets("LIST2").Range("F9").Value = myCell(1, 9).Value
> > Worksheets("LIST2").Range("F10").Value = myCell(1, 10).Value
> > Worksheets("LIST2").Range("F11").Value = myCell(1, 11).Value
> > Worksheets("LIST2").Range("F12").Value = myCell(1, 12).Value
> > Worksheets("LIST2").Range("F13").Value = myCell(1, 13).Value
> > Worksheets("LIST2").Range("F14").Value = myCell(1, 14).Value
> > Worksheets("LIST2").Range("C4").Value = myCell(1, 15).Value
> > Worksheets("LIST2").Range("E3").Value = myCell(1, 16).Value
> >
> > End If
> > Next myCell
> >
> > End Sub
> >
> >
> > --
> > Many thanks
> >
> > hazel
>
> Hazel,
>
> I won't say that this is very elegant code. But without making many
> changes to your code. You need to declare common variable rowcnt and
> add one button ( commandbutton2) and have a procedure associated to it.
> You could add commandbutton1 and associate your procedure to it. Click
> cmdbtn2 to go to next row and then click cmdbtn1 to run your macro to
> fetch values from next row ( selected by cmdbtn2) and replace on List1.
> Your LIST2 code sheet should look like this. You
>
> Dim rowcnt As Integer
> _______________________________________________________________
>
> 'Sub Macro1() ( I am changing this line of yours to associate to
> cmdbtn1)
>
> Sub CommandButton1_click()
> Dim myCell As Range
>
> For Each myCell In Worksheets("LIST1").Range("A" & (2+rowcnt))
> If myCell.Value <> "" Then
>
> Worksheets("LIST2").Range("C6").Value = myCell(1, 2).Value
> Worksheets("LIST2").Range("C7").Value = myCell(1, 3).Value
> Worksheets("LIST2").Range("C8").Value = myCell(1, 4).Value
> Worksheets("LIST2").Range("C9").Value = myCell(1, 5).Value
> Worksheets("LIST2").Range("C10").Value = myCell(1, 6).Value
> Worksheets("LIST2").Range("F8").Value = myCell(1, 7).Value
> Worksheets("LIST2").Range("C11").Value = myCell(1, 8).Value
> Worksheets("LIST2").Range("F9").Value = myCell(1, 9).Value
> Worksheets("LIST2").Range("F10").Value = myCell(1, 10).Value
> Worksheets("LIST2").Range("F11").Value = myCell(1, 11).Value
> Worksheets("LIST2").Range("F12").Value = myCell(1, 12).Value
> Worksheets("LIST2").Range("F13").Value = myCell(1, 13).Value
> Worksheets("LIST2").Range("F14").Value = myCell(1, 14).Value
> Worksheets("LIST2").Range("C4").Value = myCell(1, 15).Value
> Worksheets("LIST2").Range("E3").Value = myCell(1, 16).Value
>
> End If
> Next myCell
>
> End Sub
>
> _______________________________________________________
>
> CommandButton2_click
> rowcnt=rowcnt+1
> MsgBox " LIST1 Row No. " & (rowcnt+2)
> End Sub
>
> The way you are addressing the cells on LIST1 myCell As A2 and then
> using myCell(1,2), myCell(1,3) etc which is using offset addressing to
> A2 could be some what confusing. But if you are getting correct results
> for your first row A2 the way you expect then it is OK. Assuming you are
> getting correct results for first row I have not changed your code at
> all, except Range("A2:A2") to Range ("A" & (2+rowcnt)).
>
> A V Veerkar
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=511012
>
>