Macro to pull every Nth row of data

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

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

Thanks.
 
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
 
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
 
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
 
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/e...macro-pull-every-nth-row-data.html#post563506
Next i
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Amy said:
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?

Thanks.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Compile Data from Different Sheets 2
macro to cut/ paste ever nth row 2
Macro to Combine Data from Multiple Sheets with Cell References 4
how to copy every nth column of a row to the first cell of the row? 1
Run Macro (VBA) every 5 minutes 1
using vlookup in a loop 2
Macro to Pull data from multiple excel sheets 3
switch to another worksheet, then return to original sheet 2

Back
Top