Export values from sheets - but not hidden rows

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
950
Office Version
  1. 2019
Platform
  1. Windows
Hey all,

I developed this function which works great. The only caveat is that I would like the hidden rows in the original workbook to be deleted in the new workbook. Any ideas would be much appreciated.

VBA Code:
Sub export_values()
Dim sourceWB As Workbook
Dim destWB As Workbook
Dim ws As Worksheet
Dim path As String
Dim fname As String
Dim sh As Worksheet
Dim i As Integer
Dim shp As Shape

If MsgBox("This procedure will export the values of all sheets to a new workbook." & vbCr & vbCr & _
"Are you sure you wish to proceed?", vbYesNo + vbInformation, "") = vbNo Then Exit Sub

path = ThisWorkbook.path & "\"
fname = "values_" & Format(Now, "dd_mmm_yy_hh_mm_ss") & ".xlsb"

Set sourceWB = ThisWorkbook
 
Set destWB = Workbooks.Add
destWB.SaveAs path & fname, FileFormat:=xlExcel12
    
For Each ws In sourceWB.Worksheets

If CLng(ws.Range("B1").value) = "2" Then
i = i + 1
ws.Copy After:=destWB.Sheets(destWB.Sheets.Count)

End If
        
Application.StatusBar = "Processing..." & ws.Name
Next ws

For Each sh In destWB.Worksheets
With sh.UsedRange
.value = .value
End With
 
For Each shp In sh.Shapes
    shp.Delete
Next shp
    
Application.StatusBar = "Converting..." & sh.Name
Next sh

destWB.Save

ThisWorkbook.Activate

MsgBox i & " sheets have been successfully exported to a new workbook.", vbInformation, ""


End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Copy the visible rows data below all the original data. Then delete all original data rows both hidden and visible.

VBA Code:
With sh.UsedRange
    .SpecialCells(xlCellTypeVisible).Copy
    .Offset(.Rows.Count).PasteSpecial xlPasteValues
    .EntireRow.Delete
End With
 
Upvote 0
hmm.. the problem with this approach is that the original formatting of the cells is not retained. Also it has an issue when the panes are frozen and then you need to manually unhide rows.
 
Upvote 0
hmm. If the rows are hidden with a filter, filter for the oppisite and delete the visible rows then turn off the filter
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top