sheets(N*).printout problem

[Paulo]

why do I get erro #9 @ printing?
i have a other sub that runs the same line in the local woorkbook and ptints
just fine.
I thank in advance

Sub Imprimidor()

' On Error GoTo Fim

Dim x As Long
Dim Y As Long
Dim Formul As String
Dim Seq As Long
Dim Adit As String
Dim Clie As String
Dim Caminho As String
Y = Range("E1")
For x = Y To 27
Seq = Cells(x, "a").Value
Adit = Cells(x, "b").Value
Clie = Cells(x, "c").Value
Range("D" & x).Value = "ok"

Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _
& Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 -
N.xls"
Debug.Print Caminho
Workbooks.Open Caminho
ActiveWorkbook.UnprotectSharing
If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess
'If ActiveWorkbook.ProtectStructure = True Then
Sheets(4).Unprotect "CECAPF"
Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)"
Workbooks.Open(Filename:= _
"C:\Arquivos de programas\Microsoft
Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _
).RunAutoMacros Which:=xlAutoOpen
Calculate
'Sheet1.Range("c2").Select
''Workbooks(Caminho).Save
'Printing
Worksheets(4).PrintOut
MsgBox "Vire A página"
Worksheets(2).PrintOut
Worksheets(4).Protect "CECAPF"
Worksheets(1).Range("c2").Select
'Workbooks(Caminho).Save
Workbooks(Caminho).Close SaveChanges:=True
Next
end sub

 

[The Code Cage Team]

Error 9 as you put it can mean many things, one of the most common would be
if the worksheet you are referencing did not exist, are you sure the
workbook/worksheet you are calling to print exists or is vivible?

Regards,
The Code Cage Team
www.thecodecage.com/forumz

 

[JLGWhiz]

Usint the worksheet index, i.e. Worksheets(4) requires that four worksheets
be in the work book. If you had four sheets and deleted sheet 1, 2 or 3, you
might think that Sheet4 is the same as Worksheets(4), but it is not. The
index number runs from left to right for the sheet tabs as they appear in the
window, including any hidden sheets. If you have Sheet1(visible),
Sheet2(visible), Sheet3(hidden), and Sheet4(visible) in that order, then
Sheet4 is Worksheets(4). But if Sheet3 had been deleted instead of hidden,
then Sheet4 becomes worksheets(3). And using Worksheets(4).PrintOut would
produce error 9,

 

[Paulo]

thank you for the ilustrative explanation. but none of the them happen in my
case.

I have 100 workbooks with the same structure.
6 sheest, none have macro, so the probably dont have hidden sheets. other
than

The error happends in the printout part, all the rest of the code goes
smoothly.
if i were mistaken on the Sheets(n*) , i woul have
Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)"
on the wrong sheet, and that didnt happen.

i have changed this lines to doublecheck - but no joy, erro #9
Worksheets("CAL.ECON.").PrintOut
MsgBox "Vire A página"
Worksheets("DETALHADA").PrintOut
Worksheets("CAL.ECON.").Protect "CECAPF"
Worksheets(1).Range("c2").Select
'Workbooks(Caminho).Save
Workbooks(Caminho).Close SaveChanges:=True

 

[JLGWhiz]

Where you have these lines:

''Workbooks(Caminho).Save
'Printing
Worksheets(4).PrintOut


For testingk, modify to this:

''Workbooks(Caminho).Save
MsgBox ActiveWorkbook.Name
'Printing
Worksheets(4).PrintOut

If the workbook name displayed in the message box is not the one with the
sheet to copy then you will need to activate the correct workbook.

 

[Paulo]

I am not sure if I am able to express my self correctly.
''Workbooks(Caminho). is working fine.
I have been able to open "caminho", unprotect Sheet(4), change range value @
sheet(4), protect sheet(4), ,and save the workbook . when it gets to the
printing part, that is literaly after all this other steps that I just named,
I get the error #9.

 

[JLGWhiz]

Error 9 occurs when the compiler cannot find the object that your code tells
it to look for, in this case, worksheets(4). The only reason that it would
not find worksheets(4) is that the active workbook does not contain a
worksheets(4). In your code you have this snippet:

Workbooks.Open(Filename:= _
"C:\Arquivos de programas\Microsoft
Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _
).RunAutoMacros Which:=xlAutoOpen

Which appears to open other workbooks. If I am correct, then the last
workbook opened will be the active workbook, and might not be the workbook
that you are trying to print from. All of the other thingss that you say are
working occur in your code before that that particular snippet, so it could
be the cause of the problem. The message box insert was meant to verify that
the correct workbook is the active workbook at the time the PrintOut command
is executed. There is, otherwise, no reason why the sheet should not print.

 

[The Code Cage Team]

Paulo is adding the microsoft library reference ATPVBAEN.XLA which is the VBA
call for the add-in ANALASYS TOOLPAKm but again should not make a difference,
why not try running the code to print Sheet 4 on its own after all the above
code has run, just call another sub with sheet4 printout in it and see what
happens.

Regards,
The Code Cage Team
www.thecodecage.com/forumz

 

[Paulo]

I have tried printiout somewher else out of this code. works just fine.

i also noticed that after callin ATPVBAEM , when i debug print
Application.ActiveWindow.Caption
it tells me that I am @ ATPVBAEM .xla
i am guessing I have to call back the "caminho" workbook.

 

[Code Cage Team]

Paulo, that is your problem, you are trying to print sheet4 of the xla
(add-in) which doesn't exist so you would have to do something like:
Workbooks("Camino").Activate before the printout line!

 

[JLGWhiz]

It sometimes takes a while for the message to get through.