I added some comments to code below and copied formula instead of value.

Sub SortColumns()

With Sheets("Sheet1")
'copy columns A - c from sheet 1 to sheet 2
.Columns("$A:$C").Copy _
Destination:=Sheets("Sheet2").Columns("$A")

'row.count is Last row on worksheet
'Search Up column A from Last Row until data is found
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'Move column D,E and then column F,G
For ColCount = 4 To 6 Step 2
RowCount = 1
'Loop until no data is found in column D and F (Value of ColCount)
Do While .Cells(RowCount, ColCount) < ""

'Get Data from Sheet1
Key = .Cells(RowCount, ColCount)
MyFormula = .Cells(RowCount, ColCount + 1).Formula

With Sheets("Sheet2")
'check if Key is in column A
Set c = .Columns("A").Find( _
what:=Key, _
LookIn:=xlValues, _
lookat:=xlWhole)

'check if Key is found. Will be nothing if not found
If c Is Nothing Then
'If column F check if data is in column D
If ColCount = 6 Then
'Look for Key in Column D
Set c = .Columns("D").Find( _
what:=Key, _
LookIn:=xlValues, _
lookat:=xlWhole)

'check if Key is found. Will be nothing if not found
If c Is Nothing Then
'Write Key and fomula to sheet 2 (Col D,E or F,G) in
New row
.Cells(NewRow, ColCount) = Key
.Cells(NewRow, ColCount + 1).Formula = MyFormula
'Increment Row to put data on Sheet2
NewRow = NewRow + 1
Else
'Write Key and fomula to sheet 2 (Col D,E or F,G)
'Write data to row where key was found in FIND function
above
.Cells(c.Row, ColCount) = Key
.Cells(c.Row, ColCount + 1).Formual = MyFormula
End If
Else
'Write Key and fomula to sheet 2 (Col D,E or F,G) in New row
.Cells(NewRow, ColCount) = Key
.Cells(NewRow, ColCount + 1).Formula = MyFormula
NewRow = NewRow + 1
End If
Else
'Key was found in Column A
'Write Key and fomula to sheet 2 (Col D,E or F,G)
'Write data to row where key was found in FIND function above
.Cells(c.Row, ColCount) = Key
.Cells(c.Row, ColCount + 1).Formula = MyFormula
End If
End With
RowCount = RowCount + 1
Loop
Next ColCount
End With

End Sub



"K" wrote:

Thanks lot Joel your code works perfectly fine. I just have two
questions that i got formulas like (=1000-200) etc in column E and G.
Is it possible that when macro copy data to Sheet 2 it should keep
formulas in these columns instead of just values. Like if i have
formula in Sheet 1 column E cell which is (=1000-200) so when macro
copy data to Sheet 2 it should keep this formula in column E as
(=1000-200) instead of putting value (800). I know you can only see
the result in cell but when you select that cell you can see its
formula aswell in above formula bar so thats why i need formulas to be
in cell. And second question is that i really liked the way you
solved my problem is it possilbe for you explain me bit more that how
your code is doing its work, just for my knowledge. Thanks again for
your help