Hi there and welcome to VBAX
I'll be honest, it doesn't make a lot of sense (tho, that could just be me). I kind of get a vague idea of what you want to do but I can't work out the criteria for what needs to be applied and programming is all about working out rules for things.
The best way to work out what code to write is to set out your rules in "pseudo-code", for example:
with all the cells used in sheet1 column1
if its value is less than 500 then it goes in the corresponding row of sheet2 column3
otherwise it goes in the corresponding row of sheet2 column4
This would translate roughly to something like
Dim c As Range
With ActiveWorkbook
For Each c In .Sheets(1).Range("myNamedRange").Cells
If c.Value < 500 Then
.Sheets(2).Cells(c.Row, 3).Value = c.Value
Else
.Sheets(2).Cells(c.Row, 4).Value = c.Value
End If
Next
End With
The translation to VB is quite straight forward - the hard work is nailing the rules and criteria and the trick is to get that worked out before you worry too much about how to code it.
I hope this helps you some... if you can get us the specifics of how you want it to work, there's plenty of people here happy to help you code it!