Failure vba code

[SimbadS]

Hi,
I have this code that fails and gives med errorcode "1004" and stops at ".value = .Value"
Point is to make a copy of open sheets to a new workbook whitout formulas and just numbers.

Here is the code:

Sub New_workbook()
   Dim Ary As Variant
   Dim Ws As Worksheet
   Dim i As Long
   
   ReDim Ary(1 To Worksheets.Count)
   Sheets("Hidden_sheet").Visible = True
   For Each Ws In Worksheets
   
      If Ws.Visible = xlSheetVisible Then
   
         Select Case Ws.Name
         
            Case "Start_sheet"
            Case Else
               i = i + 1
               Ary(i) = Ws.Name
         End Select
         
      End If
      
   Next Ws
  Sheets("Hidden_sheet").Visible = False
  
   ReDim Preserve Ary(1 To i)
   Application.Calculation = xlManual
   
   Sheets(Ary).Copy
   For Each Ws In Worksheets
      With Ws.UsedRange
         .Value = .Value
      End With
  Sheets("Hidden_sheet").Visible = True
   Next Ws
   
   Application.Calculation = xlAutomatic

End Sub

 

[EXCEL MAX]

Hello Sinbad'S,
if you still not resolved error I have create new piece of code for you.

Sub CopyVisibleSheets()
        
        Dim varWorksheet As Worksheet
        Dim varNLoops As Integer
        Dim varActiveWorkbook, varNewWorkbook As Workbook
        Dim varSheetName As String
        
        Application.ScreenUpdating = False
        
        Set varActiveWorkbook = ActiveWorkbook
        Workbooks.Add
        Set varNewWorkbook = ActiveWorkbook
        
        Application.DisplayAlerts = False
        Sheets(2).Delete
        Sheets(2).Delete
        
        varActiveWorkbook.Activate
        For Each varWorksheet In Worksheets
                If varWorksheet.Visible = xlSheetVisible Then
                        varWorksheet.Activate
                        varSheetName = ActiveSheet.Name
                        varWorksheet.UsedRange.Copy
                        varNewWorkbook.Activate
                        ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
                        Application.CutCopyMode = False
                        ActiveSheet.Name = varSheetName
                        varNewWorkbook.Sheets.Add
                        varActiveWorkbook.Activate
                End If
        Next varWorksheet
        
        varNewWorkbook.Activate
        ActiveSheet.Delete
        Range("A1").Select
        
        Application.ScreenUpdating = True
        
End Sub