run time error 91 problem

Hello stevesunfold,

The werea couple of problems. The range POnumber is a named range on "Pruchase Order". It is actually cell $K$12. This cell also has a custom format that adds "ST " to the PO number. You had this in your code...

'define initial variables (note: the ranges may need to be changed
    JobNum = Range("G23").Value
    PONum = "ST" & Range("k12").Value
    SuppliersName = Range("B16")
    EndOfNameAndPath = SuppliersName & " Purchase Order " & PONum & Format(Date, " dd.mm.yy") & ".xls"

What that line should be is...

    POnum = POnumber

Another problem was a public variable PONUMBER was declared as a Range. This created a naming conflict between the named range and the public variable. I have made all the changes, but haven't run the code. Test out the attached workbook and let me know how it works.

Sincerely,
Leith Ross

hi leith
and thanks for your efforts
im getting run time error 424
object required

if i click the debug
it highlights this line

PORow = PONUMBER.Value + 1

im sorry i didnt put in the destination address into b69 and b70

im guessing you understand what i want it to do

i want it to open as a purchase order with them number already in the cell
i then want it to print 2 copies
it then has to save it in the folder as determined by the address in cell b69 or b70
it then looks for the folder as per cell ref g23
and saves it there

it then clears all the cells i need cleared

and then shows the next available po number

ive merged the two codes

and getting run time error 424 object required

again i press the debug and it highlights this

PORow = PONUMBER.Value + 1

here is my full code

Sub COPYandPRINT()
     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
     ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=False
     
     PORow = PONUMBER.Value + 1
     
     Sheets("PURCHASE ORDER NUMBERS").Range("A" & PORow).Value = PONUMBER
     Sheets("PURCHASE ORDER NUMBERS").Range("B" & PORow).Value = SUPPLIER
     Sheets("PURCHASE ORDER NUMBERS").Range("C" & PORow).Value = xDATE
     Sheets("PURCHASE ORDER NUMBERS").Range("D" & PORow).Value = JOBNUMBER
     Sheets("PURCHASE ORDER NUMBERS").Range("E" & PORow).Value = CUSTOMERNAME
     Sheets("PURCHASE ORDER NUMBERS").Range("F" & PORow).Value = DESCRIPTION
     Sheets("purchase order").Select
     
'declare all variables
Dim stOfPath As String
Dim ActualMidFolder As String
Dim EndOfNameAndPath As String
Dim FileNameToSave As String
Dim JobNum As String
Dim PONum As String
Dim SuppliersName As String

'define initial variables (note: the ranges may need to be changed
    JobNum = Range("G23").Value
    '>>> PONum = "ST" & Range("k12").Value
    PONum = PONUMBER   'POnumber is a named range = 'Purchase Order'!$K$12
    SuppliersName = Range("B16")
    EndOfNameAndPath = SuppliersName & " Purchase Order " & PONum & Format(Date, " dd.mm.yy") & ".xls"

'test to see if it is stock or PO (?) & define the saving path accordingly
    Select Case UCase(Left(JobNum, 1)) = "J"
        Case True
'Rob's test path:     stOfPath = "C:\Documents and Settings\HP_Owner\My Documents\"
            stOfPath = Range("B69") & "\"
            'test for any likely folders
                If Not (DoesFileFolderExist(stOfPath & JobNum & "*")) Then GoTo TheEnd
            'identify the exact folder
                ActualMidFolder = GetActualFolderName(stOfPath, JobNum) & "\"
            'define rest of file name
                EndOfNameAndPath = "Docs\" & EndOfNameAndPath
                FileNameToSave = stOfPath & ActualMidFolder & EndOfNameAndPath
        Case False
            stOfPath = Range("B70") & "\"
            If Not (DoesFileFolderExist(stOfPath)) Then GoTo TheEnd
                FileNameToSave = stOfPath & EndOfNameAndPath
    End Select

'save the file & finish macro
    ActiveWorkbook.SaveAs Filename:=FileNameToSave
    Exit Sub

TheEnd:
'warn nothing exists & end the macro
    MsgBox "Macro ending b/c no Folder with the following number exists : " & stOfPath & JobNum & "*", , "FYI"
    Debug.Print stOfPath & JobNum & "*"
End Sub

Public Function DoesFileFolderExist(strfullpath As String) As Boolean
'sourced from www.excelguru.ca/node/30 by Ken Puls
'note it only checks for the existence of the lowest folder (or the file) in the strfullpath string.
If Not Dir(strfullpath, vbDirectory) = vbNullString Then DoesFileFolderExist = True
End Function

Function GetActualFolderName(StartOfPath As String, StartOfFuzzyFolder As String)
'sourced & modified from http://www.themssforum.com/ExcelProgramming/parent-folder/
Dim oFSO As Object
Dim SubDirectory
Set oFSO = CreateObject("Scripting.FileSystemObject")
With oFSO.GetFolder(StartOfPath)
    For Each SubDirectory In .SubFolders
        If UCase(Left(SubDirectory.Name, Len(StartOfFuzzyFolder))) = UCase(StartOfFuzzyFolder) Then
            GetActualFolderName = SubDirectory.Name
            Exit For
        End If
    Next SubDirectory
End With
'free memory
Set oFSO = Nothing
End Function



     
     Range("B16:F19,K17:L20,B23:C24,G23:H24,I23:K24,B28:L57").Select
     Range("B16").Activate
     Selection.ClearContents
     Application.ScreenUpdating = True
     PONUMBER = PONUMBER + 1
     Range("B16").Select
     Application.DisplayAlerts = False
End Function

find attached

leith

in b69 i have

C:\Documents and Settings\my name\Desktop\master documents\job folder

in b70 i have

C:\Documents and Settings\my name\Desktop\master documents\stock purchase orders

Hello stevesunfold,

Ok, this version should work.

Sincerely,
Leith Ross

Hello stevesunfold,

Are you using the updated file? I set up folders on my hard drive to test the macro and it works fine. What macro are you running?

Sincerely,
Leith Ross