Copy Data By Number Values In Column To New Sheets

cleaco · Apr 13th 2008

I've come across the post by Dangelor but can't reply to it directly so have started a new thread quoting the code.

I'm trying to select entire rows of data based on specific values in a column and then paste those rows to a new worksheet.

This code loops 10 times and creates 10 new sheets. Any chance someone could explain some of the code to me and adapt it to suit my situation?:

Data will be in sheet 1 ("Data List"). I want search down the rows and if the value in column 2 is "1" copy that row to the sheet named "Heat 1", if the value is "2" then copy that row intt the sheet "Heat 2", etc.

Code

Sub FindandCopyRows() 
    Dim Data As Variant 
    Dim DataFound() As Variant 
    Dim iValue As Integer 
    Dim j As Long 
    Dim i As Integer 
     
    Application. ScreenUpdating = False 
    For iValue = 1 To 10 
        With Worksheets("Main") 'change  name as needed
            .Select 
            Data = .UsedRange.Value 
        End With 
        Redim DataFound(1 To UBound(Data)) 
        For j = 1 To UBound(Data, 1) 
            On  Error Resume Next 
            If Data(j, 2) = iValue Then DataFound(j) = 1 
        Next j 
        For j = 1 To UBound(Data, 1) 
            If Not DataFound(j) = 1 Then 
                For i = 1 To UBound(Data, 2) 
                    Data(j, i) = "" 
                Next i 
            End If 
        Next j 
         ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) 
        With ActiveSheet 
            .Name = "Sheet " & iValue 'change name as needed
            .Range(Cells(1, 1), Cells(UBound(Data, 1), UBound(Data, 2))).Select 
            Selection = Data 
            Selection.Sort Range("a1") 
            .Range("a1").Select 
        End With 
    Next iValue 
End Sub

Display More

Any help would be much appreciated.

**StephenR** · Apr 13th 2008

Re: Select Row By Specific Value Then Copy To Another Sheet

I would use AutoFilter. See the attachment. Note that this assumes you do not have any completely blank rows.

Code

Sub Macro2()
 
Dim i As Long, ws As Worksheet
 
With Sheets("Data List")
    For i = 1 To 10
        .AutoFilterMode = False
        .Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:=i
        Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        .AutoFilter.Range.Copy ws.Range("A1")
        ws.Name = "Heat " & i
        .ShowAllData
    Next i
    .AutoFilterMode = False
End With
   
End Sub

Display More

EDIT: naughty, cross-post: http://www.excelforum.com/showthread.php?t=641792

VBA Noob · Apr 13th 2008

Re: Select Row By Specific Value Then Copy To Another Sheet

Thanks StephenR,

cleaco,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

PM Dave Hawley if you agree to the rules

VBA Noob

Copy Data By Number Values In Column To New Sheets

Participate now!

Share