vba copy/paste visible cells giving strange results

[chocoholic1973]

I have a portion of a macro that is giving me strange results. What it is supposed to do is autofilter by the first column (numerical value) and copy those lines to a new worksheet. When I copy the visible cells, it is changing the header lines to TRUE. The red line below is where it is happening. Anyone have any suggestions? Is it the macro itself or something with my computer? I need it to be able to run on 2007 sp1.

With wsSheet
Set rnStart = .Range("A1")
Set rnData = .Range(.Range("B1"), .Range("K65536").End(xlUp))
End With
For i = 1 To B
wsSheet.Select
'Here we filter the with the first criterial.
rnStart.AutoFilter field:=1, Criteria1:=i
'Copy the filtered list
rnData = Selection.SpecialCells(xlCellTypeVisible).Copy
'Add a new worksheet to the active workbook.
Worksheets.Add Before:=wsSheet
'Name the added new worksheets.
ActiveSheet.Name = i
'Paste the filtered list.
ActiveSheet.Paste
Range("A1").Select
ActiveSheet.Name = i
Next i
End Sub

 

[AlphaFrog]

Try this...

    With wsSheet
        Set rnStart = .Range("A1")
        Set rnData = .Range(.Range("B1"), .Range("K65536").End(xlUp))
    End With
    For i = 1 To B
        'Here we filter the with the first criterial.
        rnStart.AutoFilter Field:=1, Criteria1:=i
        'Add a new worksheet to the active workbook.
        Worksheets.Add Before:=wsSheet
        'Name the added new worksheets.
        ActiveSheet.Name = i
        'Copy\Paste  the filtered list
        rnData.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=ActiveSheet.Range("A1")
        ActiveSheet.Range("A1").Select
    Next i

 

[svkroy]

I have a portion of a macro that is giving me strange results. What it is supposed to do is autofilter by the first column (numerical value) and copy those lines to a new worksheet. When I copy the visible cells, it is changing the header lines to TRUE. The red line below is where it is happening. Anyone have any suggestions? Is it the macro itself or something with my computer? I need it to be able to run on 2007 sp1.

With wsSheet
Set rnStart = .Range("A1")
Set rnData = .Range(.Range("B1"), .Range("K65536").End(xlUp))
End With
For i = 1 To B
wsSheet.Select
'Here we filter the with the first criterial.
rnStart.AutoFilter field:=1, Criteria1:=i
'Copy the filtered list
rnData = Selection.SpecialCells(xlCellTypeVisible).Copy
'Add a new worksheet to the active workbook.
Worksheets.Add Before:=wsSheet
'Name the added new worksheets.
ActiveSheet.Name = i
'Paste the filtered list.
ActiveSheet.Paste
Range("A1").Select
ActiveSheet.Name = i
Next i
End Sub

I modified it a bit and it ran fine.

Dim rnstart As Variant
Dim rndata As Variant
Dim wssheet As Worksheet

Set wssheet = Sheets("NO")
With wssheet
Set rnstart = Range("A1")
Set rndata = Range(Range("B1"), Range("K65536").End(xlUp))
End With
wssheet.Select
rnstart.AutoFilter field:=1, Criteria1:="i"
rndata = Selection.SpecialCells(xlCellTypeVisible).Copy
Worksheets.Add Before:=wssheet
ActiveSheet.Name = "i"
ActiveSheet.Paste
Range("A1").Select
ActiveSheet.Name = "i"

 

[chocoholic1973]

Thank you for the suggestions -
I have gotten the loop to work and do what it is supposed to but each time a new tab is added there is a pause. The more worksheets added the longer the pause(I only start with 2 and I have only gone up to 9 before it crashes). Should I be clearing out the clipboard (or something)?
The whole for loop is long (mainly for formating of the new worksheet), but the problem area is listed below. maybe the whole copy/paste visible cells can be said some how else that is easier on the computer/memory??

With wsSheet
Set rnStart = .Range("A1")
Set rnData = .Range(.Range("B1"), .Range("K65536").End(xlUp))
End With

B = Range("A65536").End(xlUp).Cells.Value

For i = 1 To B
Worksheets.Add Before:=wsSheet
'Name the added new worksheets.
ActiveSheet.Name = i
'Paste the filtered list.
wsSheet.Select
'Here we filter the first criterial.
rnStart.AutoFilter Field:=1, Criteria1:=i
'Copy the filtered list
Range("A1").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
Selection.Copy
'Add a new worksheet to the active workbook.
Sheets(i).Select
Range("A1").Select
ActiveSheet.paste