1st Sub works the 2nd Sub a Run Time Error

**Random_dave** · 10-11-2012, 02:51 AM

Hi All,

First ever post from me as I can't find the solution anywhere! Hope it's up to standard

I have two subs that are very similar. 2nd sub was a copy of the 1st with a few changes and bits taken out.

2nd sub gets a Run time error:

Run-time error '-2147024773 (8007007b)':

Automation error
The filename, directory name, or volume label syntax is incorrect.

I think it might have something to do with the title of the Sub 'Private Sub SkyInvoicing()' as I changed it to 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' which didn't have the Run Time error but instead brought up the list of macros to run but did not list this one.

Hope you can help,
David

First Sub (that works) is here:

Private Sub RepInvoicing()

'Prevents macro from being seen in action
    Application.ScreenUpdating = False

'Selects the invoicing sheet
    Sheets("1").Activate

' Definitions
    Dim c As Variant
    Dim cell As Range
    Dim SrchRng As Range
    Dim SrchStr As String
    Dim RangeDel As Range
    Dim InvoiceName As String
    Dim InvoiceNumber As String
    Dim cRows As Integer
    Dim iRow As Integer

'Resets iRow
    iRow = 4
    
'Loops process for each person
For Each cell In Worksheets("Comparison Sheet").Range("C4", Worksheets("Comparison Sheet").Range("C50").End(xlUp))
     
'Sets the Year and Week details for the invoice number
        Sheets("1").Range("A95") = Sheets("Comparison Sheet").Range("AL1")
        Sheets("1").Range("B93") = Sheets("Comparison Sheet").Range("AN1")
       
'Transfers Sales persons information accross to invoice sheet
   
        Sheets("Comparison Sheet").Rows("3:3").Copy
        Sheets("1").Range("98:98").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        Sheets("Comparison Sheet").Rows(iRow).Copy
        Sheets("1").Range("99:99").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
'Transfers total payment value to another cell to be checked below
    Sheets("1").Range("D93") = Sheets("1").Range("E53").Value
    
'Checks if they have anything to be paid for
    If Sheets("1").Range("D93") = "0" Then
        'Marks Comparison Sheet to say a invoice has been created for that person
        Sheets("Comparison Sheet").Range("X" & iRow) = "N/A"
        
    Else
           
'Selects copies and pastes all the names and roles from the table.

        Sheets("Comparison Sheet").Range("Table1[[Employee Name]:[Field Manager]]").Copy
        Sheets("1").Range("K100").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
        Sheets("Comparison Sheet").Range("Table1[Total Sales Qty]").Copy
        Sheets("1").Range("N100").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
'Deletes all the rows that do not contain the field managers name if they are a field manager
        Set SrchRng = Sheets("1").Range("M100:M200")
        SrchStr = Sheets("1").Range("A2").Text
        For Each c In SrchRng
            If c.Value <> SrchStr Then
                Sheets("1").Range(c.Address).EntireRow.ClearContents
            End If
        Next c
        SrchRng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp
                
'Puts in Fm team names and sale qtys into invoice
        Sheets("1").Range("K100:K111").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("1").Range("B36").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
        Sheets("1").Range("N100:N111").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("1").Range("A36").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
             
'Hides Field Manager Overrides if person is not a FM

        If Sheets("1").Range("D99") = "Field Manager" Then
            Rows("33:48").Hidden = False
        Else
            Rows("33:48").Hidden = True
        End If
    
'Sets details for the folders
    InvoiceName = Sheets("1").Range("A2").Text
    InvoiceNumber = Sheets("1").Range("E2").Text
    
'Saves Invoice as a pdf in the reps named folder
        On Error Resume Next
        MkDir ("C:\Users\Brydons\Dropbox\Integrate Admin\Reps Invoicing\FY 2012-2013\" & InvoiceName)
        On Error GoTo 0
    
        ActiveSheet.PageSetup.PrintArea = "$A$1:$E$63"
        With ActiveSheet.PageSetup
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\Brydons\Dropbox\Integrate Admin\Reps Invoicing\FY 2012-2013\" & InvoiceName & "\" & InvoiceNumber & ".pdf", Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        ActiveWorkbook.Save
        
'Changes Invoice Number
        Sheets("1").Range("C94") = Sheets("1").Range("C94") + 1
        
'Marks Comparison Sheet in invoiced to say a invoice has been created for that person
        Sheets("Comparison Sheet").Range("X" & iRow) = "Yes"

'Ends the check if they have anything to be paid for
    End If
    
'Moves invoive reference to next row
    iRow = iRow + 1
    
'Continues loop
    Next cell
    
'Selects the Comparison Sheet
Sheets("Comparison Sheet").Activate
    
'Reverses the prevention of the macro from being seen in action
    Application.ScreenUpdating = True
        
'Msg Box to let you know the invoicing is done
MsgBox ("Invoicing is all done :)")

End Sub

2nd Sub (thats broken) is here:

Private Sub SkyInvoicing()

'Prevents macro from being seen in action
    Application.ScreenUpdating = False

'Selects the invoicing sheet
    Sheets("2").Activate

' Definitions
    Dim InvoiceName As String
    Dim InvoiceNumber As String
    Dim cRows As Integer
    Dim iRow As Integer
 
'Resets iRow 
    iRow = 4
    
'Loops process for each region
For Each cell In Worksheets("Comparison Sheet").Range("AJ4", Worksheets("Comparison Sheet").Range("AJ13").End(xlUp))
        
'Sets the Year and Week details for the invoice number
        Sheets("2").Range("A95") = Sheets("Comparison Sheet").Range("AL1")
        Sheets("2").Range("B93") = Sheets("Comparison Sheet").Range("AN1")
       
'Transfers Sales persons information accross to invoice sheet
   
        Sheets("Comparison Sheet").Rows("3:3").Copy
        Sheets("2").Range("98:98").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        Sheets("Comparison Sheet").Rows(iRow).Copy
        Sheets("2").Range("99:99").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
'Transfers total payment value to another cell to be checked below
    Sheets("2").Range("D93") = Sheets("2").Range("K39").Value
    
'Checks if they have anything to be paid for
    If Sheets("2").Range("D93") = "" Then
        'Marks Comparison Sheet to say a invoice has been created for that person
        Sheets("Comparison Sheet").Range("AN" & iRow) = "N/A"
        
    Else
           
'Sets details for the folders
    InvoiceName = Sheets("2").Range("AJ99").Text
    InvoiceNumber = Sheets("2").Range("L2").Text
    
'Saves Invoice as a pdf in the reps named folder
        On Error Resume Next
        MkDir ("C:\Users\Brydons\Dropbox\Integrate Admin\Sky Invoicing\FY 2012-2013\" & InvoiceName)
        On Error GoTo 0
    
        ActiveSheet.PageSetup.PrintArea = "$A$1:$K$53"
        With ActiveSheet.PageSetup
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\Brydons\Dropbox\Integrate Admin\Sky Invoicing\FY 2012-2013\" & InvoiceName & "\" & InvoiceNumber & ".pdf", Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        ActiveWorkbook.Save
        
'Changes Invoice Number
        Sheets("1").Range("C94") = Sheets("1").Range("C94") + 1
        
'Marks Comparison Sheet in invoiced column to say a invoice has been created for that person
        Sheets("Comparison Sheet").Range("AN" & iRow) = "Yes"

'Ends the check if they have anything to be paid for
    End If
    
'Moves invoive reference to next row
    iRow = iRow + 1
    
'Continues loop
    Next cell
    
'Selects the Comparison Sheet
Sheets("Comparison Sheet").Activate
    
'Reverses the prevention of the macro from being seen in action
    Application.ScreenUpdating = True
        
'Msg Box to let you know the invoicing is done
MsgBox ("Sky Offices invoicing is all done :)")

End Sub

**Sindhus** · 10-11-2012, 03:21 AM

Hi Random_dave,
Welcome to the forum

Private sub worksheet_change() get automatically triggers, when any change happens in the sheet. Where exactly you are getting the error in second sub (By pressing F8 you can run line by line and you can find which line triggers the error).

**Random_dave** · 10-11-2012, 03:25 AM

Hi Sindhus,

Thanks for the F8 tip. Will come in handy for sure.
It is the first line that has the error: 'Private Sub SkyInvoicing()'
Can't understand why it would do this seeing that it is similar to the other title.

I just to 'Private Sub test()' but this didn't work either??

Cheers,
David

**Sindhus** · 10-11-2012, 03:37 AM

Please share your sheet with macro and dummy data.

**Random_dave** · 10-11-2012, 03:49 AM

Hope this works. I've gone through and removed any sensitive data.

Example Workbook.xlsm

Update: I've just tried this file as it is posted here and both subs now get the run time error! Crap.....

**Sindhus** · 10-11-2012, 04:35 AM

Hi I have checked your code. I am getting error in the place you are exporting it as pdf. I am not getting any error other than that. You can try the add-in suggested in this link to get rid of it. http://www.mrexcel.com/forum/excel-q...or-5-help.html