Macro to pull every Nth row of data
 

I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.

Macro to pull every Nth row of data
 

Amy

How about

Sub Test()
Source_Sheet = "Sheet1"
Target_Sheet = "Sheet2"

n = 5000 ' your last line of data on Sheet1
Target_Row = 1
Sheets(Target_Sheet).Select
For nCount = 1 To n Step 60

Worksheets(Source_Sheet).Cells(nCount, 1).EntireRow.Copy
Worksheets(Target_Sheet).Cells(Target_Row, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Target_Row = Target_Row + 1
Next

End Sub

I've used paste values here so that calculations on your first sheet are not
compromised in the second

"Amy" wrote:

I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.

Macro to pull every Nth row of data
 

you don't need a macro to achieve that

provided the data is in Sheet1!A1:A100 in Sheet2!B1 try this formula:

=OFFSET(Sheet1!$A$1,ROW()*60-1,)

drag down


On 19 Lis, 14:43, Amy wrote:
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.

Macro to pull every Nth row of data
 

Sub copyalternaterows()
Dim ds As Worksheet
Dim r As Long
Dim i As Long
Set ds = Sheets("Sheet6") 'destination
With Sheets("sheet7") ' source
r = 2
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 60
.Rows(i).Copy ds.Rows(r)
r = r + 1
Next i
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Amy" wrote in message
...
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.

Macro to pull every Nth row of data
 

Many thanks to all...

This worked best for me.


Sub copyalternaterows()
Dim ds As Worksheet
Dim r As Long
Dim i As Long
Set ds = Sheets("Sheet6") 'destination
With Sheets("sheet7") ' source
r = 2
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 60
..Rows(i).Copy ds.Rows(r)
r = r + 1
Original Source: The Code Cage Forums
http://www.thecodecage.com/forumz/ex...tml#post563506
Next i
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Amy" wrote in message
...
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.

I didn't go with the Offset fx because I have about 20 worksheets a week
that need this and I just want to push a button and it be done.

Thanks again.
Amy



"Jarek Kujawa" wrote:

you don't need a macro to achieve that

provided the data is in Sheet1!A1:A100 in Sheet2!B1 try this formula:

=OFFSET(Sheet1!$A$1,ROW()*60-1,)

drag down


On 19 Lis, 14:43, Amy wrote:
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.

.

Macro to pull every Nth row of data
 

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Amy" wrote in message
...
Many thanks to all...

This worked best for me.


Sub copyalternaterows()
Dim ds As Worksheet
Dim r As Long
Dim i As Long
Set ds = Sheets("Sheet6") 'destination
With Sheets("sheet7") ' source
r = 2
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 60
.Rows(i).Copy ds.Rows(r)
r = r + 1
Original Source: The Code Cage Forums
http://www.thecodecage.com/forumz/ex...tml#post563506
Next i
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Amy" wrote in message
...
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.

I didn't go with the Offset fx because I have about 20 worksheets a week
that need this and I just want to push a button and it be done.

Thanks again.
Amy



"Jarek Kujawa" wrote:

you don't need a macro to achieve that

provided the data is in Sheet1!A1:A100 in Sheet2!B1 try this formula:

=OFFSET(Sheet1!$A$1,ROW()*60-1,)

drag down


On 19 Lis, 14:43, Amy wrote:
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.

.