If the every third column is just an example then the following
macro would probably work:


Option Explicit

Sub copyNBentries()
Dim rng As Range, cell As Range, oldname As String
Dim i As Long, newsht As Worksheet
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
Dim x As String
If rng.Cells.Count < 2 Then
MsgBox "single cell range is unacceptable for selection"
Exit Sub
End If
oldname = ActiveSheet.Name
'Create New Sheet
Sheets.Add After:=Sheets(Sheets.Count) '-- place at end
'Rename current Sheet
ActiveSheet.Name = Trim(oldname & "_" & _
Format(0 + Now(), "yyyymmddhhmmss"))
For Each cell In rng

If Replace(cell.Value, " ", "") < "" Then
i = i + 1
On Error Resume Next
ActiveSheet.Cells(i, 1).Value = cell.Value
If Err.Number < 0 Then
ActiveSheet.Cells(i, 3) = Err.Number & " " & Err.Description
End If
If cell.HasFormula Then ActiveSheet.Cells(i, 2).Formula = "'" & cell.Formula
End If
Next cell
Exit Sub
End Sub

If you're not familiar with macros see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I can get the macro to fail (noted in column C) with a formula in the worksheet
like ="= " and don't know why.
1004 Application-defined or object-defined error

Because of 0 and space in some cells due to formulas,
I added column B to show formula, and column C to show errors
You can delete columns B & C if you are satisfied with result.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"choice" wrote in message ...
i have
5 4 7 9 (non adjascent columns, 3 columns apart each)
8 6 9 1
4 1 7 3

how do i go about copying this data, then pasting it all in one row (does not matter which order)

5
4
7
9
8
6
9
1
4
1
7
3