SaveFile with a Cell value name - Subscript out of range (Error 9)

[JhouwaS]

Hi guys,
I'm beginner with VB and i'm doing a code to save a plan with a specific name that will be in "D7" cell, "D8" cell and so on. Each cell should save a different file.
The problem is: when it try to save the erros "Subscript out of range (Error 9)" appears.

Sub HONEY4008()
'
' Macro1 Macro
'


Dim ABA As String, CELULA As String, DIRETORIO As String
Dim a As Integer, j As String

j = Clear
a = Str(a)
For a = 7 To Cells(2, 4).Value
Cells(3, 26) = Cells(a, 4).Text 'Cell Z3 receive D7 values e.g

Windows("DM4008_PLANILHA CONTROLE .xlsm").Activate
ABA = "RESUMO"
CELULA = "Z3"

DIRETORIO = "C:\Documents and Settings\JhouwaS\My Documents\amanda\MACRO\"
savename = Sheets(ABA).Range(CELULA).Text 'STOP Here
If Directory = "" Then Directory = CurDir & "\"
On Error GoTo errorsub:

Windows("DM4008_MODELO.xlsx").Activate
Cells(a, 4).Select
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xls"
ActiveWindow.Close
' MsgBox "fim do codigo", vbCritical
Next a
Exit Sub


End Sub

What should I do to solve?

Tks
JhouwaS

 

[VoG]

Is the sheet definitely called "RESUMO" not " RESUMO" or "RESUMO "

 

[wigi]

Try to troubleshoot using:

Sub HONEY4008()

    MsgBox Workbooks("DM4008_PLANILHA CONTROLE .xlsm").Sheets("RESUMO").Range("Z3").Value

End Sub

Also, please indent your code. Finding errors will become easier.

Wigi

 

[JhouwaS]

Helo Wigi and VoG,
I put the code that you suggest, but still have the same problem:
"Run time error '9'
Subscription out of range
The sheet "RESUMO" was ok without spaces

 

[wigi]

Do you refer to a workbook that is in the SAME Excel application?

 

[VoG]

That error means that a sheet with that name does not exist in the active workbook.