Getting run time error 9 when running on other PC's
Private Sub CommandButton1_Click()
Dim x As Integer
Dim y As Integer
Dim a As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim n As Integer
Dim RNG As Range
Dim LRow As Integer
Dim ILRow As Integer
Dim xPath As String
Dim Ws As Worksheet
Dim Wb As Workbook
Dim FName As String
Dim FIsm As String
Range("A2:A2000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _
), UNIQUE:=True
Worksheets("INPUT").Range("O1:O2000").Copy
Worksheets("UNIQUE").Range("A1:A2000").PasteSpecial
Worksheets("UNIQUE").Range("A2:A2000").Copy
Worksheets("UNIQUE").Range("A1:A1999").PasteSpecial
ILRow = Worksheets("INPUT").Cells(Worksheets("INPUT").Rows.Count, "A").End(xlUp).Row
Worksheets("INPUT").Range("O1:O" & ILRow).Clear
Sheets("INPUT").Activate
With ActiveSheet
x = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
xPath = Application.ActiveWorkbook.Path
'Copying values from main input to MTO sheet
y = WorksheetFunction.CountA(Worksheets("UNIQUE").Range("A1:A2000")) 'counting numbers of non empty cells in range
Worksheets("UNIQUE").Range("F1") = x
Worksheets("UNIQUE").Range("F2") = y
For j = 1 To y
Worksheets("MTO_T").Range("A1:E500").Clear
'ILRow = Worksheets("INPUT").Cells(Worksheets("INPUT").Rows.Count, "A").End(xlUp).Row
a = 2
For i = 2 To x
If Worksheets("INPUT").Range("A" & i) = Worksheets("UNIQUE").Range("A" & j) Then
Worksheets("MTO_T").Range("A" & i).Value = Application.WorksheetFunction.VLookup(Worksheets("UNIQUE").Range("A" & j), Worksheets("INPUT").Range("A" & (a) & ":K" & ILRow), 11, False)
Worksheets("MTO_T").Range("b" & i).Value = Application.WorksheetFunction.VLookup(Worksheets("UNIQUE").Range("a" & j), Worksheets("INPUT").Range("A" & (a) & ":K" & ILRow), 8, False)
Worksheets("MTO_T").Range("c" & i).Value = Application.WorksheetFunction.VLookup(Worksheets("UNIQUE").Range("a" & j), Worksheets("INPUT").Range("A" & (a) & ":K" & ILRow), 9, False)
End If
a = a + 1
Next i
Worksheets("MTO_T").Activate
LRow = Worksheets("MTO_T").Cells(Worksheets("MTO_T").Rows.Count, "A").End(xlUp).Row
Set RNG = Worksheets("MTO_T").Range("A1:A" & LRow).SpecialCells(xlCellTypeBlanks)
RNG.EntireRow.Delete
LRow = Worksheets("MTO_T").Cells(Worksheets("MTO_T").Rows.Count, "A").End(xlUp).Row
Worksheets("MTO_T").Cells(1, 20) = LRow / 18
Worksheets("MTO_T").Cells(1, 21).Formula = "=CEILING(RC[-1],1)"
m = Worksheets("MTO_T").Cells(1, 21)
'copying from MTO_ to MTO
k = 0
For n = 1 To m
For i = 1 To 18
Worksheets("MTO").Range("C" & (i + 4)) = Worksheets("MTO_T").Range("A" & (i + k))
Worksheets("MTO").Range("I" & (i + 4)) = Worksheets("MTO_T").Range("B" & (i + k))
Worksheets("MTO").Range("J" & (i + 4)) = Worksheets("MTO_T").Range("C" & (i + k))
Next i
k = (n * 18)
Worksheets("UNIQUE").Cells(j, 9) = n
Worksheets("UNIQUE").Cells(j, 10).Formula = "=concatenate(RC[-9],""."",RC[-1])"
'If m <= 1 Then
'FName = Worksheets("UNIQUE").Range("A" & j)
'FName = Worksheets("UNIQUE").Range("A" & j)
'Else:
FName = Worksheets("UNIQUE").Range("J" & j)
'End If
Set Wb = Workbooks.Add
ThisWorkbook.Sheets("MTO").Copy Before:=Wb.Sheets(1)
Application.DisplayAlerts = False
For Each Ws In Sheets
If IsEmpty(Ws.UsedRange) Then Ws.Delete
Next
Application.DisplayAlerts = True
Wb.SaveAs xPath & "\" & FName & ".xlsx"
ActiveWorkbook.Close
Workbooks("MTO").Worksheets("INPUT").Activate
Next n
Next j
End Sub
Bookmarks