#REF! help please

[TheMilkGuy]

Hi Folks,

Using this code, I'm copying a range of cells from one sheet to
another (Thanks to Simon for the code)
~~~~~~~~~~~
Public Sw As Long
Sub HideRows()
Dim Rng As Range, MyCell As Range
Set Rng = Sheets("Sheet1").Range("B29:B" & Range("B" &
Rows.Count).End(xlUp).Row)
If Sw = 1 Then
Rng.Rows.Hidden = False
Sw = 0
Exit Sub
End If
For Each MyCell In Rng
If MyCell.Value = 0 Then
MyCell.Rows.Hidden = True
Sw = 1
End If
Next MyCell
Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy
Destination:=Sheets("Sheet2").Range("A3")
End Sub
~~~~~~~~~~~~~

The problem is that the new sheet contains mostly #REF! errors. I
have tried xlCellTypeAllFormatConditions instead but I get a VB error
that says "Run-time error '1004': Application-defined or object-
defined error"

Any suggestions? If you cannot tell, I am a VBA dolt.

Many thanks!
Craig

 

[Joel]

It looks like you have formulas that need to be change to values. I made a
few simply changes to use PasteSpecial

Public Sw As Long
Sub HideRows()
Dim Rng As Range, MyCell As Range
Set Rng = Sheets("Sheet1").Range("B29:B" & Range("B" & _
Rows.Count).End(xlUp).Row)
If Sw = 1 Then
Rng.Rows.Hidden = False
Sw = 0
Exit Sub
End If
For Each MyCell In Rng
If MyCell.Value = 0 Then
MyCell.Rows.Hidden = True
Sw = 1
End If
Next MyCell
Set MyRange = Rng.SpecialCells(xlCellTypeVisible)
MyRange.Copy
Sheets("Sheet2").Range("A3").PasteSpecial _
Paste:=xlPasteValues
End Sub

 

[TheMilkGuy]

Just about perfect Joel!

How could I modify this code to copy columns A through T to Sheet2
instead of just column B?

Thanks!
Craig

 

[Joel]

from
MyRange.Copy
to
MyRange.EntireRow.Copy


If it is copying to many columns then it is easy to delete the extra columns

Sheets("Sheet2").columns("U:IV").delete