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
Bookmarks