Help Required By Relative Newcomer

[Guest]

Hi There

I am trying to write a loop that will go between a range on a sheet (i.e. A2:A34) and as it moves through the loop I wish to select and copy the value in that cell and then paste onto another worksheet where a macro will recalculate the data and then print the sheet, then I would like that loop to start over again but on the next cell down and run until it reaches the range end?

Or

Is there a way of creating a list of constant values in which a loop can access and place in the other sheet as it moves through them (i.e. comma separated list where the loop would drop the comma as it moved through the list)?

Sorry if I am not explaining myself correctly but if anyone could help then I would be ever so grateful.

Thanks in advance!!!!

Alistair Hutton

 

[Frank Kabel]

Hi
the following will loop through your range and change cell
A1 on your second sheet (and print this sheet)

sub foo()
dim source_wks as worksheet
dim target_wks as worksheet
dim source_rng as range
dim target_rng as range
Dim cell as range
with activeworkbook
set source_wks=.worksheets("sheet1")
set target_wks=.worksheets("sheet2")
end with
set source_rng = source_wks.range("A2:A34")
set target_rng = target_wks.range("A1")#

For each cell in source_rng
if cell.value<>"" then
with target_wks
target_rng.value=cell.value
..calculate
..printout
end with
end if
next
end sub

ta

-----Original Message-----
Hi There

I am trying to write a loop that will go between a range

on a sheet (i.e. A2:A34) and as it moves through the loop
I wish to select and copy the value in that cell and then
paste onto another worksheet where a macro will
recalculate the data and then print the sheet, then I
would like that loop to start over again but on the next
cell down and run until it reaches the range end?

Or

Is there a way of creating a list of constant values in

which a loop can access and place in the other sheet as it
moves through them (i.e. comma separated list where the
loop would drop the comma as it moved through the list)?

Sorry if I am not explaining myself correctly but if

anyone could help then I would be ever so grateful.

 

[Guest]

Hi Frank

I would like to thank you for your help with my problem - when i see your code I seen how close I was and how simple it was!!!! Oh well i'll know for the next time

Thanks once again