+ Reply to Thread
Results 1 to 2 of 2

Compile error argument not optional

  1. #1
    Registered User
    Join Date
    09-07-2015
    Location
    auckland, new zealand
    MS-Off Ver
    2013
    Posts
    1

    Compile error argument not optional

    I am getting an Error message as above when stepping through a macro which attempts to call this macro.
    Any help greatly received.


    Call AA_MakeArray

    Windows("" & N & " One Year Report.xlsx").Close
    Next i
    End Sub


    Sub AA_MakeArray(N As Variant)
    'Dim Sh As String


    AA = Array(Array("Acc1", "Acc2", "Acc3", "Acc4", "Acc5"), Long list works ok)

    BB = Array( “accounting”, long list – works oK)


    Windows("" & N & " One Year Report.xlsx").Activate
    For i = 0 To UBound(AA, 1)
    ActiveSheet.Activate
    ' Set values
    A = AA(i)
    B = BB(i)

    Found = False

    ' Open workbook to copy to
    Workbooks.Open Filename:="E:\Dropbox\& 00 macro\Colour.xlsx"
    Windows("" & N & " One Year Report.xlsx").Activate

    ' Select existing sheets
    For j = 0 To UBound(A, 1)
    Sh = A(j)
    ' If not found yet
    If Not Found Then
    If SheetExists(Sh) Then
    Sheets(Sh).Activate
    Sheets(Sh).Select
    Found = True
    Sheets(Sh).Copy Before:=Workbooks("Colour.xlsx"). _
    Sheets(Workbooks("Colour.xlsx").Sheets.Count)
    Windows(N & " One Year Report.xlsx").Activate
    End If
    Else
    ' Check if exists before selecting
    If SheetExists(Sh) Then
    Sheets(Sh).Select (False)
    Sheets(Sh).Copy Before:=Workbooks("Colour.xlsx"). _
    Sheets(Workbooks("Colour.xlsx").Sheets.Count)
    Windows(N & " One Year Report.xlsx").Activate
    End If
    End If
    Next j

    ' Print if found
    If Found Then

    ' Export PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "E:\Dropbox\" & N & "\2014 One Year Reports\" & N & " One Year Report " & B & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

    'Select and export as separate excel delete extra rows and columns and change color and save NEED to run page select before delitng shets or cahnge reference


    ' Copy to workbook
    Windows("Colour.xlsx").Activate
    Sheets("Sheet1").Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True



    Sheets.Select
    Columns("A:X").Select
    Range("X62").Activate
    Selection.Delete Shift:=xlToLeft

    Rows("69:78").Select
    Selection.Delete Shift:=xlUp

    ActiveWindow.SmallScroll Down:=-30
    Rows("1:38").Select
    Range("A38").Activate
    Selection.Delete Shift:=xlUp

    Call Printing_Area_Thirty

    'Create new folder to save Excel workbooks
    'MkDir "E:\Dropbox\" & & N & & "\2014 Teacher Comment Detailed by Subject"

    ' Save excel
    ActiveWorkbook.SaveAs Filename:= _
    "E:\Dropbox\" & N & "\2014 Teacher Comment Detailed by Subject\" & N & " Teacher Comment Detailed " & B & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    ActiveWorkbook.Save

    ' Export Subject Teacher Comment PDF
    ActiveWorkbook.Sheets.Select


    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "E:\Dropbox\" & N & "\2014 Teacher Comment Detailed by Subject\" & N & " Teacher Comment Detailed " & B & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False


    ActiveWorkbook.Close
    End If
    Next i

    Next k

    End Sub

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,987

    Re: Compile error argument not optional

    which line produces the error?

    ps you'll need to edit your post to put the code within the Code tags (highlight all of the code and click the # in the formatting options above.
    If someone has helped you then please add to their Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. i am getting Compile error: Argument not optional
    By baig123 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-26-2014, 06:27 AM
  2. Compile Error: Argument not optional
    By no.18shirt in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-09-2013, 10:59 AM
  3. [SOLVED] Compile error: Argument not optional
    By darkhangelsk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-03-2013, 06:12 AM
  4. Argument not optional - Compile error
    By dsrfinancial in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2012, 07:34 AM
  5. Compile error: Argument not optional
    By dabrows in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2011, 11:29 AM
  6. [SOLVED] Compile Error Argument Not optional
    By Bouzy210@gmail.com in forum Excel General
    Replies: 1
    Last Post: 08-16-2006, 12:05 PM
  7. [SOLVED] Compile Error: Argument not optional
    By Brett Smith in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-19-2006, 01:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1