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