issue with xlsheetveryhidden

[bhandari]

i have some data in Output and Input sheets

my intention to use xlsheetveryhidden for both sheets,because user not required to see both sheets

but this sheet is interlinked to another sheets

if this sheets are "xlsheetveryhidden" my macro is not running properly
if they are visible
my code working properly

any solution for this
with macro?

 

[bhandari]

can i get any professional help with this?

 

[Scott T]

Please post the code so people can see what you are trying to do.

 

[bhandari]

Sub CreateSheets()
    
    Dim X           As Long
    Dim wks         As Worksheet
    Dim MyFormulas  As Variant
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
      
    With Sheets("BOQ")
        For X = 5 To .Cells(5, Columns.Count).End(xlToLeft).Column
            If Len(.Cells(5, X).Value) > 0 Then
                On Error Resume Next
                Set wks = Sheets(CStr(.Cells(5, X).Value))
                On Error GoTo 0
                If wks Is Nothing Then
                    Sheets("INPUT").Copy after:=Sheets(Sheets.Count)
                    Sheets(Sheets.Count).Name = .Cells(5, X).Value
                    Set wks = ActiveSheet
                    MyFormulas = Array("='" & wks.Name & "'!$I$65", "", "='" & wks.Name & "'!$I$80", "='" & wks.Name & "'!$I$88", "='" & wks.Name & "'!$I$94", "='" & wks.Name & "'!$I$99", "", "='" & wks.Name & "'!$I$111", "='" & wks.Name & "'!$I$117", "='" & wks.Name & "'!$I$121", "", "='" & wks.Name & "'!$I$127", "='" & wks.Name & "'!$I$132", "='" & wks.Name & "'!$I$134", "='" & wks.Name & "'!$I$135", "='" & wks.Name & "'!$I$138", "='" & wks.Name & "'!$I$141", "='" & wks.Name & "'!$I$144", "", "='" & wks.Name & "'!$I$149", "='" & wks.Name & "'!$I$150", "='" & wks.Name & "'!$I$151", "='" & wks.Name & "'!$I$152", "='" & wks.Name & "'!$I$155", "='" & wks.Name & "'!$I$158", "='" & wks.Name & "'!$I$164", "='" & wks.Name & "'!$I$167", "='" & wks.Name & "'!$I$170", "='" & wks.Name & "'!$I$173", "='" & wks.Name & "'!$I$124", "='" & wks.Name & "'!$I$125", "=1")
                    .Range("A9").Offset(, X - 1).Resize(32, 1).Formula = Application.Transpose(MyFormulas)
                Else
                    MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf & "Already exists!", vbExclamation, "Sheet Exists"
                End If
            End If
            Set wks = Nothing
        Next X
    End With
    
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

here is the code

 

[Scott T]

The only way to copy a sheet that is hidden that I know of is to unhide it then hide it again.

Sheets("INPUT").Visible = True
'do your copy here
Sheets("INPUT").Visible = xlSheetVeryHidden

You can however copy a range from a Hidden sheet, the code below worked even though BOQ was very hidden. So your other choice is copy a range instead of the whole sheet.

Sub test()
Sheets("BOQ").Range("E5:S5").Copy Sheets("Sheet10").Range("E5")
End Sub

 

[bhandari]

i understood that without unhide we cant run the macro

i have to unhide and hide it
if possible can u add your code in my code it is easy to understand.

I hope yor second code works

 

[Scott T]

This should work

Sub CreateSheets()
    
    Dim X           As Long
    Dim wks         As Worksheet
    Dim MyFormulas  As Variant
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
      
    With Sheets("BOQ")
        For X = 5 To .Cells(5, Columns.Count).End(xlToLeft).Column
            If Len(.Cells(5, X).Value) > 0 Then
                On Error Resume Next
                Set wks = Sheets(CStr(.Cells(5, X).Value))
                On Error GoTo 0
                If wks Is Nothing Then
                    Sheets("INPUT").Visible = True
                    Sheets("INPUT").Copy after:=Sheets(Sheets.Count)
                    Sheets(Sheets.Count).Name = .Cells(5, X).Value
                    Set wks = ActiveSheet
                    MyFormulas = Array("='" & wks.Name & "'!$I$65", "", "='" & wks.Name & "'!$I$80", "='" & wks.Name & "'!$I$88", "='" & wks.Name & "'!$I$94", "='" & wks.Name & "'!$I$99", "", "='" & wks.Name & "'!$I$111", "='" & wks.Name & "'!$I$117", "='" & wks.Name & "'!$I$121", "", "='" & wks.Name & "'!$I$127", "='" & wks.Name & "'!$I$132", "='" & wks.Name & "'!$I$134", "='" & wks.Name & "'!$I$135", "='" & wks.Name & "'!$I$138", "='" & wks.Name & "'!$I$141", "='" & wks.Name & "'!$I$144", "", "='" & wks.Name & "'!$I$149", "='" & wks.Name & "'!$I$150", "='" & wks.Name & "'!$I$151", "='" & wks.Name & "'!$I$152", "='" & wks.Name & "'!$I$155", "='" & wks.Name & "'!$I$158", "='" & wks.Name & "'!$I$164", "='" & wks.Name & "'!$I$167", "='" & wks.Name & "'!$I$170", "='" & wks.Name & "'!$I$173", "='" & wks.Name & "'!$I$124", "='" & wks.Name & "'!$I$125", "=1")
                    .Range("A9").Offset(, X - 1).Resize(32, 1).Formula = Application.Transpose(MyFormulas)
                Else
                    MsgBox "Sheets: " & .Cells(5, X).Value & vbCrLf & vbCrLf & "Already exists!", vbExclamation, "Sheet Exists"
                End If
            End If
            Set wks = Nothing
            Sheets("INPUT").Visible = xlSheetVeryHidden
        Next X
    End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[bhandari]

consider #1
sheet tab names are not changing

i have changed properties then i used your code
may be u missed "output" sheet
because of this sheet tab names are missing

 

[Scott T]

The code posted does not do anything to "output". You are getting an error because the INPUT sheet is hidden when you try to copy it. The only difference with your original code and the one I posted is the unhiding and hiding of the INPUT sheet so that it can be copied. What properties did you change?

 

[bhandari]

I have done "xlsheetveryhidden" in properties for both then i click on run your code
if output sheet is "xlsheetvisible" then
my code is running perfect.
it have some data which is linked to it,
i want to use "xlsheetveryhidden" for this sheet also