copy worksheet without showing hidden rows

meghanalissa

New Member
Joined
Jan 14, 2013
Messages
20
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.

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

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
This should be what you're after...

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

New Member
Joined
Jan 14, 2013
Messages
20
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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...

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

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

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