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]

spent too much time not worked i have cleard all data in output also even though i check all macros "output" didnt linked with any sheet now
..still without output sheet my code not working properly
Help me out

There is a issue with code only

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

if i delete "output sheet" iam getting sheet name "INPUT" name only,instead of coming range names iam getting like this names for sheet tabs
INPUT(1)
INPUT(2)
INPUT(3)

 

[Scott T]

On the BOQ sheet what is in the cells on the 5th row? That is where the names for the new sheet come from.

 

[bhandari]

i changed that names too without thet "output sheet" i cant do anything
i dont know where it is linked exactly
if it is exist then working properly

 

[bhandari]

here is link
https://drive.google.com/open?id=0B-ZWlAv5DqaETlBVYWxVOFRzUHM
Please delete that "Chainages" previous post i have said "OUTPUT" sheet

 

[Scott T]

The code runs without error for me without any output sheet and just stuff typed into the 5th row of BOQ. I believe your problem could be with the BOQ sheet not pulling data correctly to put into the 5th row. Sounds like you need the output sheet to populate the row. Are there formulas in the row or is it a different piece of code?


Edit: I can not download files at work. Maybe some else can look at it.

 

[bhandari]

i have deleted few sheets i gave link to u...now its working fine..iam a big noob