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