• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide Zeros when creating new workbook with selected sheets from an existing workbook

jonastiger

Member
Hello
Looking for a code to (a fast) create new workbook with selected sheets from an existing workbook, I found this:

Code:
Public Sub Save_Last_3_Sheets()

    Dim newWb As Workbook
    Dim newXlsxFullName As String
    Dim p As Long, i As Long
    Dim sheetName As String
    Dim currentWb As Workbook
    
    Set newWb = Workbooks.Add(xlWBATWorksheet)
    newWb.Worksheets(1).Name = "_"
    
    With ThisWorkbook
        p = InStrRev(.FullName, ".")
        newXlsxFullName = Left(.FullName, p - 1) & "values.xlsx"
        For i = .Worksheets.Count - 2 To .Worksheets.Count
            sheetName = .Worksheets(i).Name
            .Worksheets(i).Cells.Copy
            newWb.Worksheets.Add After:=newWb.Worksheets(newWb.Worksheets.Count)
            With newWb.Worksheets(newWb.Worksheets.Count)
                .Paste
                .UsedRange.Value = .UsedRange.Value
                .Range("A1").Select
                .Name = sheetName
            End With
        Next
    End With
    
    Application.CutCopyMode = False
    
    'Suppress warning for sheet deletion and for saving in case new workbook already exists
    
    Application.DisplayAlerts = False
    
    newWb.Worksheets(1).Delete
    
    On Error Resume Next
    newWb.SaveAs newXlsxFullName, FileFormat:=xlOpenXMLWorkbook
    newWb.Close SaveChanges:=False
    If Err.Number = 0 Then
        MsgBox "Saved " & newXlsxFullName, vbInformation
    Else
        MsgBox "Error saving " & newXlsxFullName & vbCrLf & vbCrLf & "Error number " & Err.Number & vbCrLf & Err.Description, vbExclamation
    End If
    On Error GoTo 0
    
    Application.DisplayAlerts = True
    
End Sub

It works fine and as wanted. The only issue is that the sheets in the new workbook displays zeros and I don't want that.
I´ve tried to add ActiveWindow.DisplayZeros=False, but returns error

I would appreciate your help
Thanks in advance
JT
 
Hello, just try to Activate the worksheet before using DisplayZeros property …​
Hi
I've tried
Code:
...
.Worksheets(i).Cells.Copy
With .Worksheets(i).Activate
.DisplayZeros=False
End With
Still gives error, I'm doing this wrong I guess
 
According to VBA help - a must read ! - your code has no sense !​
You must first activate the worksheet like .Worksheets(i).Activate then you could try ActiveWindow.DisplayZeros = False …​
 
Back
Top