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.
0
Utf
11/19/2009 1:43:02 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
2878 Views

Similar Articles

[PageSpeed] 57

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.
0
Utf
11/19/2009 2:00:07 PM
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 <A...@discussions.microsoft.com> wrote:
> I have a spreadsheet of data and I need to pull every 60th row out onto
> another sheet. Any simple macros?
>
> Thanks.

1
Jarek
11/19/2009 2:15:59 PM
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
dguillett1@austin.rr.com
"Amy" <Amy@discussions.microsoft.com> wrote in message 
news:E7F31354-DF6B-44A9-A0EB-01531B78181F@microsoft.com...
>I have a spreadsheet of data and I need to pull every 60th row out onto
> another sheet. Any simple macros?
>
> Thanks. 

0
Don
11/19/2009 2:40:00 PM
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/excel-vba-programming/155475-macro-pull-every-nth-row-data.html#post563506
Next i
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Amy" <Amy@discussions.microsoft.com> wrote in message
news:E7F31354-DF6B-44A9-A0EB-01531B78181F@microsoft.com...
>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 <A...@discussions.microsoft.com> wrote:
> > I have a spreadsheet of data and I need to pull every 60th row out onto
> > another sheet. Any simple macros?
> >
> > Thanks.
> 
> .
> 
0
Utf
11/19/2009 3:02:03 PM
Glad to help
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Amy" <Amy@discussions.microsoft.com> wrote in message 
news:3340D3B4-531C-47FA-A7F7-B0A52395E6D9@microsoft.com...
> 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/excel-vba-programming/155475-macro-pull-every-nth-row-data.html#post563506
> Next i
> End With
> End Sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "Amy" <Amy@discussions.microsoft.com> wrote in message
> news:E7F31354-DF6B-44A9-A0EB-01531B78181F@microsoft.com...
>>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 <A...@discussions.microsoft.com> wrote:
>> > I have a spreadsheet of data and I need to pull every 60th row out onto
>> > another sheet. Any simple macros?
>> >
>> > Thanks.
>>
>> .
>> 

0
Don
11/19/2009 3:34:04 PM
Reply:

Similar Artilces:

summarizing worksheet data
Hi, I want to create what should be a relatively simple spreadsheet with, say, fifty worksheets in the workbook. Each worksheet would be identical in terms of number of rows & columns, as well as the type of data those columns contain. On the first worksheet, however, I'd like to be able to summarize the cumulative totals of all the other sheets. So, for instance, if cell a20 on worksheets 2 through 50 contained a formula that calculated a sum of the figures in a1:a19, I'd like to put a formula in a cell in the first worksheet that will total the cell a20 in all the other w...

Gathering and adding data from different rows
Hi all, I run a report at work every day that tells me how many shares of a company have been sold and from what year these shares were granted to the participant. The year and the amount of shares are on different lines. For instance, the year would be on D5 and the share amount would be on D10. This is a daily function and the data changes every day. I am required to manually scan these reports and add up the shares for each year separately. I have to separate the amounts for 2003, 1997 and 1996. I want to create a spreadsheeet that I can dump this data into and have the spreadsheet find...

macro to exit design mode
I am creating protected worksheets for users. When some users open them, their Excel is already in Design Mode. I need code to 'exit' design mode automatically. Can anyone advise please? -- cm see if this helps: Sub ExitDesignMode() With CommandBars("Exit Design Mode").Controls(1) If .State = msoButtonDown Then .Execute End With End Sub -- jb "cm" wrote: > I am creating protected worksheets for users. When some users open them, > their Excel is already in Design Mode. > > I need code to 'exit&#...

How do I insert a row ...
I want to insert a blank row every two rows, with the consideration that my first row is the title. Thanks. Faye the best way is to go from the end of the sheet back to the beginning like this Lastrow = Range("A" & rows.count).end(xlup).row for rowcount = Lastrow to 1 step -2 Rows(RowCount).insert next rowCount -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=185766 [url=&quot;http://www.thecodecage.com/forumz/&quot;]E...

How to get every conytols
Hi Guys, I'm working on Word 2007 and creating a form input user. I'm doing this for a user that have Office 2003, so I have a compatibility mode template document. It also include VBA code. What i need to do is to read every existing text control. Theses controls are ActiveX. I always have used: For Each xControl In Controls If TypeOf xControl Is TextBox Then xControl.value="" End If Next xControl But now in Word 2007 in compatibility mode I do not know how to do it, becuase there is not "Controls" in the ActiveDocument. ...

Surpressing rows and columns with zero values in a pivot table
Let's say I want a report showing the number of times that my employees, grouped in offices, markets and regions, makes a particular kind of error. At the end of the month, I'd like to create a pivot table that says, "This office had this number of errors of this type." I can create a pivot table that does that, but it includes all the employees that have zero errors, making the report unwieldy. How can I format the pivot table to hide any row or column that has no value in it? Your issue is that it has a value. Something adding up to zero is just as va...

two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I download transactions into Microsoft Money for either account, all the transactions go to only one of the accounts set up in Money. I was prompted for the first download to our "main" account. Then when I went to download for the other account, I wasn't prompted where to download to... and all the transactions went into the first account. Did that make sense? My bank required we switch to Money from Quicken, so I am new to this. Tried to search in users guide and online for help. Help would be much appreciated - ...

Access unwanted automatic data entry
when entering data in my Access table, if i use the tab ket to advance to the next field, it often enters a number in that field. Why does it do that nad how can I make it stop! Are entering data direcctly into Access table? If so, stop doing that, unless you only do it occasionally to repair data in database and you know database design and manage it. "Pat the biologist" <Pat the biologist@discussions.microsoft.com> wrote in message news:6B724974-C937-4C5C-BDBA-1A086C0712A1@microsoft.com... > when entering data in my Access table, if i use the tab ket to advance to...

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. 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:= _ F...

Example using MFC WinInet classes with POST of FILE and other INPUT data
I am looking for a good example for POSTing both FILE and other INPUT data to a URL. I understand that the FILE must use a multi-part encoding but I do not understand how the atcual file contents get POSTed or how the INPUT data gets POSTed also. I am guessing the INPUT data can be added as part of the query parameters to the URL. ...

Q: how can I write this macro?
Hello, I am trying to copy-paste a part of an excel file based on the selected cell and create another excel file and save it. Here is the scenario: 1. I clicked E18 (this is an example, it can be D18 or others) and run the macro 2. Macro should select E1-E18 (if D18, then D1-D18) and A1-B18 (so first two columns and selected column up to the row the cell selected) 3. copy selected area 4. Create a file test.xls 5. paste it into first cell (I assume this will combine all three columns together, so the data will be in A.B and C columns, not in A,B and E (or D)) 6. save file How can I do t...

How to get userform Cancel button to invoke Exit Sub in calling macro?
I have a userform that is called from inside a sub in a module of a workbook. In order to keep most of the code inside the module, the userform only sets variables in the sub. Here is the code in the sub: 'create and initialize variables Dim Bkp,XSub as Boolean Dim BkpSfx as String Bkp = True XSub = False 'call options box BackupOptionsBox.Show 'Set main subroutine variables Bkp = BackupOptionsBox.YesOption BkpSfx = "_" & BackupOptionsBox.SfxBox.Value ...

Change height of row
Excel seems to limit the height of the row to some presets. Is there a way to set height of row to any height? Am trying to make spreadsheet line up with a pre-printed form. Hi i think the limit is 1 pixel -- Regards Frank Kabel Frankfurt, Germany "Pam MacT" <Pam MacT@discussions.microsoft.com> schrieb im Newsbeitrag news:CCAE9073-80C5-47B1-B103-7834241AF34B@microsoft.com... > Excel seems to limit the height of the row to some presets. Is there a > way > to set height of row to any height? Am trying to make spreadsheet line up > with a pre-printed form. ...

Pivot table and it's macro
I am creating a table and from the table a pivot table using a macro. When I run the macro the table gets created without a problem but when it goes to create the pivot table I get an error and the debugger highlights this section: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Table8", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _ :="Perf Tables!R2C8", TableName:="PivotTable1", DefaultVersion:= _ xlPivotTableVersion12 Can anyone see where the problem is happening? My ...

Pivot table row header
Hi, In a pivot table, I have 2 fields in the row section. How can I have the value on the first fields to repeat in every row instead of just showing once? Thanks!! Try Debra Dalgleish's page at: http://www.contextures.com/xlDataEntry02.html Fill Blank Cells Fill Blank Cells Programmatically* [*Sub FillColBlanks() 'by Dave Peterson 2004-01-06] -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Jason" <Jason@discussions.microsoft.com> wrote in message news:08549CBC-68A7-442E-BABF-D48B11E29479@microsoft.co...

Averaging only some rows
I have an application where I want to calculate the average of several fields, but only in those rows where the second column is blank. For example: (A3)1 (A4)<blank> (A5)10 (A6)0 (B3)1 (B4)<blank> (B5)20 (B6)2 (C3)50 (C4)some text (C5)500 (C6)0 (D3) 1 (D4)<blank> (D5)15 (D6)4 should give me for a row of averages those calculated for all rows except C - i.e., 1, 15, 2. I appreciate any suggestions on how I can design this Excel 2007 solution. Thanks! Do you mean =AVERAGE(IF(A4:D6="",A3:D5)) as an array formula, commit with Ctrl-Shift-Enter not just Enter ...

Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front. After every week, when new data is entered, I want the formula at the front to use the cell with the new data instead of me having to change the existing formula. For example, my data looks like this: A B C 276 300 421 175 0 0 0 I need a formula that will automatically detect the last number >0 in column C. Thank you for your assistance! =Countif(C2:C1000,">0") so your main page formula might be something like: =...

Data Validation in XL2007 suddenly stops functioning
I created a workbook in XL2003 that included some named ranges on Sheet2 that fed some data validation lists on Sheet1. Everything worked as expected. My colleague opened up the workbook in XL2007 and used it several times, and everything worked as expected. Then she called me, saying that it was broken. Of the 6 named ranges and related data validation columns on the other sheet, none of them were working anymore. I went over to her PC, and checked everything I could think of- I could set up working data validation test cells on Sheet2, but could not get the ones on Sheet1 to...

Excel macros page breaks but not on row 1 and column value in footer
Hi, I have seen some posts that are similar, but no one seems to have the problem that I am having. I will post my macro in this, for anyone that is interested. My users get a csv file every month, and we have to clean it up. This macro does that. My last issues are this: 1) having the spreadsheet create page breaks whenever the value in column B changes. Below is just that code. Code: col = 2 LastRw = ActiveSheet.UsedRange.Rows.Count For X = 2 To LastRw If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1") Then ActiveWindow.SelectedSheets.HPageBreaks...

Outlook 2007 Does not pull all POP mail emails
I have Outlook 2007 running on WinXP Professional. Outlook does not pull all my mail from my POP mail accounts and when my computer is off. Even if I leave the computer on it still has issues with my emails not ending up in my Outlook. I have Outlook set to pull emails ever two minutes. I have no filters running either. I have these large gaps where no email are downloaded at all. If I look on the Gmail website at my email I see several that have not been grabbed by Outlook. Any help would be appreciated Warren -- Warren Jorgenson. Submitted using http://www.outlookforums.com...

Row Height variations from computer to computer on same workbook
I've been sent a workbook by a colleague and on his workbook, a specific row has a height of 13.8 (23 pixels) and on my machine it is 16.5 (22 pixels). Has anyone seen this before and what do we need to do to ensure that it's consistent from machine to machine? Thanks in advance, Barb Reinhardt Fix found. Display Control Panel -> Settings -> Advanced -> General -> DPI setting to Normal (96 DPI) Barb Reinhardt "Barb Reinhardt" wrote: > I've been sent a workbook by a colleague and on his workbook, a specific row >...

Appending data in cells that utilize a Dropdown list.
Hello all, I would like to append entries in cells that utilize a dropdown list. Currently, when I select (in this case names) from my dropdown list I cannot append them if I want to add more than one to a cell. If I select another name from the dropdown, it erases the first entry. Is there some way to append entries within cells that use this form of validation so that I can enter more than one in a cell from the dd list? -- Thanks, Geek using Office XP Professional Hi Geek Debra Dalgleish has an example workbook showing how to do this http://www.contextures.com/excelfiles.html...

Importing vcalendar data to multiple users
I'm running Exchange 5.5. Several times a year I have to create a large group of users. I'd like to be able to pre-populate each user's calendar with pertinent calendar events. Is there a way to import this type of data in batch mode? See if http://www.slipstick.com/calendar/holidays.htm#company helps. ME wrote: > I'm running Exchange 5.5. Several times a year I have to create a > large group of users. I'd like to be able to pre-populate each user's > calendar with pertinent calendar events. > > Is there a way to import this type of data in batch...

Pivot Table: showing data as % of subtotal
Hi, I have pivot table data in this format: Name Type Amount ABC 1 $100 2 $200 3 $300 4 $400 ABC Total $1,000 DEF 1 $150 2 $260 3 $310 4 $420 DEF Total $1,140 and so on .... .... .... I want to see the data as follows: Name Type Amount % ABC...

data validation
my excel skills is no more than having a certain cell multiplyin another cell. scenerio: i have 2 cells, with drop down box. box2 is dependant to selections o box1. after i chose the item in box2, i would like, let say, cell A3, A4, A to display certain data about the item i selected in box2. lets say, i selected "Peter" on box2, i would like A3 to display hi "sex", A4 being "age", A5 is "height". thank -- Message posted from http://www.ExcelForum.com Hi have a look at http://www.contextures.com/xlDataVal02.html for dependent data validation lis...