copy worksheet without showing hidden rows

[meghanalissa]

I need to use vba to copy a worksheet and paste its values into a new workbook in Excel 2010. Then, paste formatting from the original. The problem arises when there are hidden rows. I know the below is not the most efficient way to do this, but I want to add a statement to it to get it to not show the hidden rows. I will completely revamp the code at a later time to be more efficient.

    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:="[URL="file://\\tr\EquityDebt\TreasRpt\Website\2012PP1.xlsx"]C:\user\New.xlsx[/URL]", _
        FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ThisWorkbook.Activate
    Sheets(Array("Sheet1")).Select
    Sheets("Sheet1").Activate
    Cells.Select
    Selection.Copy
    Windows("New.xlsx").Activate
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

 

[njimack]

This should be what you're after...

Sub a()
Application.ScreenUpdating = False
Workbooks.Add
    ActiveWorkbook.SaveAs Filename:="C:\user\New.xlsx", FileFormat:=xlOpenXMLWorkbook
        
    ThisWorkbook.Activate
    
    Sheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible).Copy
    
    Windows("New.xlsx").Activate
    Selection.PasteSpecial xlValues
    Selection.PasteSpecial xlFormats
    
Application.ScreenUpdating = False
End Sub

 

[meghanalissa]

Thank you for your response...but I may not have been clear the first time. I need to copy the hidden rows to the new sheet and keep them hidden. I do not want them removed from the new copy.

 

[njimack]

The only approach I can think of is to loop through each row and test whether or not it was hidden in the original sheet. If you have tens of thousands of rows, this may take a while to run. See if the below (untested) code works for you...

Sub a()
Dim Last_Row As Long
Dim i As Long
Application.ScreenUpdating = False
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\user\New.xlsx", FileFormat:=xlOpenXMLWorkbook
        
ThisWorkbook.Activate
    
    
With Sheets("Sheet1")
    Last_Row = .UsedRange.Rows
    .UsedRange.Copy Workbooks("New.xlsx").Sheets("Sheet1").Range("A1")
    
    For i = 2 To Last_Row
        Workbooks("New.xlsx").Sheets("Sheet1").Cells(i, 1).Hidden = .Cells(i, 1).Hidden
    Next i
End With
Windows("New.xlsx").Activate
With ActiveSheet.Cells
    .Formula = .Value
End With

Application.ScreenUpdating = False
End Sub