I have rows of information (mailing list). I would like to copy every 5th
row only to a new workbook using transpose without having to highlight every
5th entry. Is this possible?

Example

Destination workboook, Sheet1, A1...

=INDEX('[Source.xls]Sheet1'!$A$1:$A$100,5*(ROWS($A$1:A1)-1)+1)

which can be copied down.

Chris H wrote:
I have rows of information (mailing list). I would like to copy every 5th
row only to a new workbook using transpose without having to highlight every
5th entry. Is this possible?

2nd or 5th?

For 2nd as Subject line reads.

In a helper column adjacent to your data.

I'll use column M for example.

Leave M1 blank, enter x in M2, blank in M3, x in M4

Select M1:M4 and drag/copy down.

Autofilter on column M for x

F5SpecialVisible cellsOK

Copy then paste into new workbook.

Then do the copypaste specialtranspose.


Gord Dibben MS Excel MVP

On Sun, 15 Nov 2009 11:32:01 -0800, Chris H <Chris
wrote:

I have rows of information (mailing list). I would like to copy every 5th
row only to a new workbook using transpose without having to highlight every
5th entry. Is this possible?

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris H" <Chris wrote in message
...
I have rows of information (mailing list). I would like to copy every 5th
row only to a new workbook using transpose without having to highlight
every
5th entry. Is this possible?

A macro will this for you
Option Explicit
Sub every5thRow()
Const WORKSHEET_NAME As String = "Sheet1"
Dim x As Long, i

x = 0
i = 1
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(WORKSHEET_NAME)

Dim rng As Range
Set rng = ws.Range(ws.Cells(1, "A"), ws.Cells(Rows.Count, "A").End(xlUp))

Dim arrnoedimensional() As Variant
ReDim arrnoedimensional(1 To rng.Cells.Count)

Dim Cell As Range
For Each Cell In rng
If i Mod 5 = 0 Then
x = x + 1
arrnoedimensional(x) = Cell.Value
End If
i = i + 1
Next

Workbooks.Add
Dim wb As Workbook
Set wb = ActiveWorkbook
ReDim Preserve arrnoedimensional(1 To x)

i = 2
Dim strValue As Variant
For Each strValue In arrnoedimensional
wb.Worksheets(1).Range("A" & i).Value = strValue
i = i + 1
Next

End Sub

"Chris H" wrote:

I have rows of information (mailing list). I would like to copy every 5th
row only to a new workbook using transpose without having to highlight every
5th entry. Is this possible?

Put this line above

If x = 0 Then Exit Sub ' Exit sub if no data
Workbooks.Add

"Chris H" wrote:

I have rows of information (mailing list). I would like to copy every 5th
row only to a new workbook using transpose without having to highlight every
5th entry. Is this possible?