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

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" wrote:

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

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

On Aug 13, 4:42*am, Joel wrote:
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" wrote:
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

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



"TheMilkGuy" wrote:

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

On Aug 13, 4:42 am, Joel wrote:
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" wrote:
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