vba copy/paste visible cells giving strange results

chocoholic1973

New Member
Joined
Mar 21, 2010
Messages
4
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
Try this...

Code:
    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

Board Regular
Joined
Sep 12, 2009
Messages
178
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

New Member
Joined
Mar 21, 2010
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,494
Members
414,071
Latest member
hijackhippo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top