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

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" wrote in message
...
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

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" wrote in message
...
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 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

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" wrote in message
...
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" wrote in message
...
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

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