I want to take a highlighted column of numbers in excel and pull out
every Nth item onto a new worksheet.  I would like a piece of VBA code
that I could attache to a button...that code would look at my
selection, prompt me for the divisor (to select every Nth row), and
copy every Nth cell onto a new worksheet (without any blank rows).  So
if I selected a column of 100 rows and wanted every 10th item...I
would select a row, hit a button in my toolbar, fill in a prompt with
"10", hit enter...and a new sheet with a single column of 10 rows with
the appropriate data would show up...

...seems pretty straightforward, but I don't have the time to remember
how to write the code...

Thanks in advance!

Place this VBA macro in a general module. It will copy every nth row
of selected data. If you select only one column it will copy only that
column. If you select more than one column it will copy all selected
columns.

Public Sub PullEveryNthRow()

' Pull every Nth row from the selected range and place those values in
a new worksheet.

   Dim Increment As Long
   Dim Source As Range
   Dim TargetSheet As Worksheet
   Dim SourceRow As Long
   Dim TargetRow As Long
   
   ' Use the current selection for the source
   Set Source = Selection
   
   ' Query user for the increment
   Do
      Increment = InputBox("Enter increment:")
      If Increment < 1 Then
         Exit Sub
      End If
   Loop Until Increment > 0

   ' Add a new sheet and move the data
   Sheets.Add
   Set TargetSheet = ActiveSheet
   TargetRow = 1
   For SourceRow = 1 To Source.Rows.Count Step Increment
      TargetSheet.Rows(TargetRow).Resize(1,
Source.Columns.Count).Value = Source.Rows(SourceRow).Value
      TargetRow = TargetRow + 1
   Next SourceRow

End Sub

Kevin

It appears that the Google Answers posting page does not handle long
lines very well. Here is the same code with continuation line syntax
added:

Public Sub PullEveryNthRow()

' Pull every Nth row from the selected range and place those values in
' a new worksheet.

   Dim Increment As Long
   Dim Source As Range
   Dim TargetSheet As Worksheet
   Dim SourceRow As Long
   Dim TargetRow As Long
   
   ' Use the current selection for the source
   Set Source = Selection
   
   ' Query user for the increment
   Do
      Increment = InputBox("Enter increment:")
      If Increment < 1 Then
         Exit Sub
      End If
   Loop Until Increment > 0

   ' Add a new sheet and move the data
   Sheets.Add
   Set TargetSheet = ActiveSheet
   TargetRow = 1
   For SourceRow = 1 To Source.Rows.Count Step Increment
      TargetSheet.Rows(TargetRow). _
         Resize(1, Source.Columns.Count).Value _
         = Source.Rows(SourceRow).Value
      TargetRow = TargetRow + 1
   Next SourceRow

End Sub

Kevin

I am reposting my response using my main user ID for the record. When
I first posted, my main ID wasn't working.

Public Sub PullEveryNthRow()

' Pull every Nth row from the selected range and place those values in
' a new worksheet.

   Dim Increment As Long
   Dim Source As Range
   Dim TargetSheet As Worksheet
   Dim SourceRow As Long
   Dim TargetRow As Long
   
   ' Use the current selection for the source
   Set Source = Selection
   
   ' Query user for the increment
   Do
      Increment = InputBox("Enter increment:")
      If Increment < 1 Then
         Exit Sub
      End If
   Loop Until Increment > 0

   ' Add a new sheet and move the data
   Sheets.Add
   Set TargetSheet = ActiveSheet
   TargetRow = 1
   For SourceRow = 1 To Source.Rows.Count Step Increment
      TargetSheet.Rows(TargetRow). _
         Resize(1, Source.Columns.Count).Value _
         = Source.Rows(SourceRow).Value
      TargetRow = TargetRow + 1
   Next SourceRow

End Sub

Kevin