Macro runs well but not when called by another ??

Hi james,

I think including a full workbook reference will solve your problem.

Workbooks(ThisFile).Sheets(Array("WON", "NROL",...etc.

Originally Posted by RHCPgergo

Hi james,

I think including a full workbook reference will solve your problem.

Workbooks(ThisFile).Sheets(Array("WON", "NROL",...etc.

Thanks RHCP but unfortunately no... just altered it to this

Sub backit2()
    Dim ThisFile As String, x As Long, ww As Window
    ThisFile = ActiveWindow.Caption
    For Each ww In Windows
        If ww.Visible = True And ww.Caption <> ThisFile Then
            Workbooks(ThisFile).Sheets(Array("WON", "NROL", "Hospitals West Midlands", "Hospitals Banbury", _
                 "SIGBOX-ECR-CONTL", "VMF", "POIC")).Copy after:=Workbooks(ww.Caption).Sheets(Workbooks(ww.Caption).Sheets.Count)
                      Windows(ThisFile).Activate
                           ActiveWindow.SelectedSheets.Visible = False
                    
                    
            
        End If
    Next ww
    
Call Sheetstohide
    
End Sub

But with no Joy :-/

Any other ideas ?

Dont know if I mentioned but its a runtime error 9 ??

thanks again

I am not 100 percent sure but it sounds like this might be a variable problem. Variables are a great tool but when you call a procedure and want that variable to be used by another procedure you have to make that variable public at the beginning of the main vba code.

So before your main code starts you would type for example

Public ww as worksheet

Sub MainVbaCode ()

This is really weird guys !

just made some alterations to the sheets to hide part as it refused to recognise a sheet to hide despite double checking name etc. and ended up just recording an extra macro that it calls lol

Anyway back to the original issue I've tried the Public ww as worksheet suggested by bstier with no difference..

So what I now have That works when ran from workSheetsShow

Public ww As Worksheet


Sub WorkSheetsShow()

Dim sh As Worksheet
For Each sh In Worksheets
    sh.Visible = xlSheetVisible
Next

Call backit2

End Sub


Sub backit2()
    Dim ThisFile As String, x As Long, ww As Window
    ThisFile = ActiveWindow.Caption
    For Each ww In Windows
        If ww.Visible = True And ww.Caption <> ThisFile Then
       Workbooks(ThisFile).Sheets(Array("WON", "NROL", "Hospitals West Midlands", "Hospitals Banbury", _
                "SIGBOX-ECR-CONTL", "VMF", "POIC")).Copy after:=Workbooks(ww.Caption).Sheets(Workbooks(ww.Caption).Sheets.Count)
                    Windows(ThisFile).Activate
                        ActiveWindow.SelectedSheets.Visible = False
                    
                    
            
        End If
    Next ww
    
Call Sheetstohide
    
End Sub


Sub Sheetstohide()
'
' Shtstohide Macro
' Macro recorded 12/02/2013 by James Riley
'
'

Worksheets("CSV01").Visible = False
Worksheets("CSV03").Visible = False
Worksheets("ITEM").Visible = False
Worksheets("WON").Visible = False
Worksheets("NROL").Visible = False
Worksheets("Hospitals west Midlands").Visible = False
Worksheets("Hospitals Banbury").Visible = False
Worksheets("SIGBOX-ECR-CONTL").Visible = False
Worksheets("VMF").Visible = False
Worksheets("POIC").Visible = False

Call Line_content

End Sub


Sub Line_content()
'
' Line_content Macro
' Macro recorded 13/02/2013 by James Riley
'

'
    Sheets("Line  Content").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

But for the life of me I can't get it to work correctly when Worsheetshow is called from the original macro that creates the new WB's??

Any Idea's as I'm going bald from pulling my hair out lol

james

Hi james,

have you tried my suggestion in comment #6?
If you don't reference a workbook fully, it will always look at the active workbook, which is not always what you want.

Originally Posted by RHCPgergo

Hi james,

have you tried my suggestion in comment #6?
If you don't reference a workbook fully, it will always look at the active workbook, which is not always what you want.

Hi RHCPgergo


I was just looking at that and think you may be right !!
Reasoning > when the initial Macro runs and creates the new WB (book1 / book2 )etc. then it takes a dive and Debugs ! however when I close the Debugger it automatically goes to the newly created WB not the initial tool with the Macro's within so I am assuming therefore that that WB is the active one when it dives??

However if I then select the original WB and run the macro's from Worksheetsshow it works perfectly

there pfore I assume Worksheet show is trying to unhide sheets where there are non hidden and then backit2 is trying to copy sheets that arent there....

PROBLEM!!!!

The Current WB or Tool is called "Week 34 possession creation tool" and have just been sent one with "WK43 Poss Tool" as its name ....

So there is no set name for the original work book !

So how do i get the start of WorkSheetShow macro to reference / run in the original WB??

Thanks

James

This is the end of the original sub which creates the new WB I think the adjustment to the code needs to be made here? to re focus the code back to the main WB once the new WB has been created ??

              With Selection.Borders(xlEdgeLeft)
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                
                With Selection.Borders(xlEdgeBottom)
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
                
                With Selection.Borders(xlEdgeRight)
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = xlAutomatic
                End With
               
                              
                      
                ActiveSheet.PageSetup.PrintArea = "$A$1:$K$" & outrow
            
            Next outlead
        
        'create new workbook
            shcnt = 0
            ReDim aShtLst(1 To 1)
        
            For Each ws In ActiveWorkbook.Worksheets
        
            WSNAME = ws.Name
               
            If Left(WSNAME, 5) = SHTPREFIX Then
                ReDim Preserve aShtLst(1 To shcnt + 1)
                shcnt = shcnt + 1
                aShtLst(shcnt) = WSNAME
            
            End If
            Next ws
            Sheets(aShtLst).Move
            
    
    Else    ' User chose No.
    Application.ScreenUpdating = True
       
    
    End If

Call WorkSheetsShow

End Sub

There is no code for creating a workbook here. It should be something like

WorkBooks.Add

Can't you post all the code that is in the module? Or post the whole workbook? It would be a lot easier to resolve any problems that way.

Hey RHCPgergo

Firstly really sorry for the late reply had to take my nipper to the hospital for CT scan on thursday and friday was day off and like an idiot I hadnt synced the file to my usb stick :-/

On a ligher note I have mangaged to resolve the program with the following Subs - they arnt perfect and would appreciate any advice you can offer with cleaning them up to run better and the Backit sub copies the sheets to all open workbooks :-/ not great but it works lol

many thanks and lioke I said any advice appreciated :-)

Sub WorkSheetsShow()

Dim sh As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook

wb.Activate

For Each sh In Worksheets
    sh.Visible = xlSheetVisible
Next

Call backit2

End Sub


Sub backit2()

    Dim ThisFile As String, x As Long, ww As Window
    ThisFile = ActiveWindow.Caption
    For Each ww In Windows
        If ww.Visible = True And ww.Caption <> ThisFile Then
       Workbooks(ThisFile).Sheets(Array("WON", "NROL", "Hospitals West Midlands", "Hospitals Banbury", _
                "SIGBOX-ECR-CONTL", "VMF", "POIC")).Copy after:=Workbooks(ww.Caption).Sheets(Workbooks(ww.Caption).Sheets.Count)
                    Windows(ThisFile).Activate
                        ActiveWindow.SelectedSheets.Visible = False
                    
                    
            
        End If
    Next ww
    
Call Sheetstohide
    
End Sub


Sub Sheetstohide()
' re hide back of pack sheets James Riley
'
'

Worksheets("CSV01").Visible = False
Worksheets("CSV03").Visible = False
Worksheets("ITEM").Visible = False
Worksheets("WON").Visible = False
Worksheets("NROL").Visible = False
Worksheets("Hospitals west Midlands").Visible = False
Worksheets("Hospitals Banbury").Visible = False
Worksheets("SIGBOX-ECR-CONTL").Visible = False
Worksheets("VMF").Visible = False
Worksheets("POIC").Visible = False
Worksheets("Works").Visible = True


End Sub

Originally Posted by RHCPgergo

There is no code for creating a workbook here. It should be something like

WorkBooks.Add

Can't you post all the code that is in the module? Or post the whole workbook? It would be a lot easier to resolve any problems that way.

I would love to help with optimizing, but please post the workbook if you want me to help, cause it's kind of hard to derive what's needed from just code samples.

I hope your son gets better, I wish him well. (I had to look up "nipper" in a dictionary :D)

Hey RHCPgergo

Sorry for late response ! I'll see if I can post the workbook up tomorrow..
The nippers are on half term break this week so I've taken a few days annual leave..

My eldest was the one at the hospital - He's 8 and unfortunately it looks like more surgery for him... Next time be his 25th operation :-(
Long term problem since birth but he's a chirpy little dude :-)) Thanks fore the kind wishes :-))

And yes sorry its just our silly English slang lol

James

Originally Posted by RHCPgergo

I would love to help with optimizing, but please post the workbook if you want me to help, cause it's kind of hard to derive what's needed from just code samples.

I hope your son gets better, I wish him well. (I had to look up "nipper" in a dictionary :D)