Selecting Data

[hugo15]

I've got a speadsheet that contains about 40,000 timepoints. What I would like to do is select the first time point and copy it to a new worksheet. I would then like to select the 50th timepoint and copy it to below the first timepoint. I then want to select the 100th timepoint, 150th timepoint, and so on until the bottom of the data is reached.

How do I do this in VBA?

 

[wigi]

For instance like this, if you use a workbook with 2 sheets:

Sub ff()
    For i = 1 To Sheets(1).Range("A" & Rows.Count).End(xlUp).Row Step 50
        Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets(1).Range("A" & i).Value
    Next
End Sub

 

[kpark91]

Assuming your data is in Sheet1
and you want to paste into Sheet2

Dim i&, count&
count = 1
For i = 1 To Worksheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row Step 50
    Worksheets("Sheet2").Range("A" & count).Value = Worksheets("Sheet1").Range("A" & i).Value
    count = count + 1
Next i

 

[GaryWMn]

This code will create the new sheet you identified, as well as give you some flexibility in how you want to copy.

I assume your data is set up something like this.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Order</td><td style="font-weight: bold;;">TimePoint</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4:19 PM</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6:14 PM</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1:55 AM</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">11:45 AM</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10:19 AM</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">10:19 PM</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;">3:07 PM</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">10:19 PM</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7:55 PM</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10</td><td style="text-align: right;;">6:28 AM</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">11</td><td style="text-align: right;;">12:28 AM</td></tr><tr ><td style="color: #161120;text-align: center;">40000</td><td style="text-align: right;;">39999</td><td style="text-align: right;;">4:48 AM</td></tr><tr ><td style="color: #161120;text-align: center;">40001</td><td style="text-align: right;;">40000</td><td style="text-align: right;;">1:26 AM</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

You can copy this code into a new module and then run it:

Sub CopyEvery50()
    Dim iMaxRows As Long
    Dim iSourceRow As Long
    Dim iSourceCol As Long
    Dim iTargetRow As Long
    Dim iTargetCol As Long
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    
[COLOR="DarkGreen"]'   Shut off screen refresh[/COLOR]
    Application.ScreenUpdating = False
    
[COLOR="DarkGreen"]'   Point to the source worksheet[/COLOR]
    Set wsSource = ActiveSheet

[COLOR="DarkGreen"]'   Determine the maximum number of rows in the source[/COLOR]
    iMaxRows = Cells(Rows.Count, 2).End(xlUp).Row
    
[COLOR="DarkGreen"]'   Add a new worksheet[/COLOR]
    Set wsTarget = Worksheets.Add
    
[COLOR="DarkGreen"]'   Initialize variables (adjust as desired)[/COLOR]
    iTargetRow = 2          [COLOR="DarkGreen"]'Start copying to the second row (below the header)[/COLOR]
    iSourceCol = 2          [COLOR="DarkGreen"]'Assume the source data is in column B (2)[/COLOR]
    iTargetCol = 1          [COLOR="DarkGreen"]'Copy data to column A (1) of the Target worksheet[/COLOR]
    
[COLOR="DarkGreen"]'   Set up the new worksheet by adding a header[/COLOR]
[COLOR="DarkGreen"]'   (Modify this to point to any other desired location)[/COLOR]
    With wsTarget.Cells(1, iTargetCol)
        .Value = "TimePoint"
        .Font.Bold = True
    End With
    
[COLOR="DarkGreen"]'   Cycle through the source rows,[/COLOR]
[COLOR="DarkGreen"]'   starting with the first data point in row 2[/COLOR]
[COLOR="DarkGreen"]'   and then selecting every 50 after that until done[/COLOR]
    For iSourceRow = 2 To iMaxRows Step 50

[COLOR="DarkGreen"]'       Copy source to target[/COLOR]
        wsSource.Cells(iSourceRow, iSourceCol).Copy _
            wsTarget.Cells(iTargetRow, iTargetCol)
        iTargetRow = iTargetRow + 1
    Next iSourceRow
    
[COLOR="DarkGreen"]'   Turn on screen refresh[/COLOR]
    Application.ScreenUpdating = True
    
[COLOR="DarkGreen"]'   Clean up[/COLOR]
    Set wsTarget = Nothing
    Set wsSource = Nothing
    
End Sub

Try it out and let us know how it works.

Gary