This is a simple For ... Next
so why am I getting the error, is it related to the function somehow?
The macro I supposed to find a specific worksheet within a large workbook, get some data and copy it to a separate workbook named after the worksheet. Most of this came from mw recording a macro with changes were necessary.
Dim wbThisWB As Workbook
Dim LastRow As Long
Dim WSName As String
Dim lRow As Long
Workbooks.Open Filename:= _
"\\Shenetapp01\itt viability and intervention\Assurance Work AY 17-18\AGR\Test\16-17 EY Trainees test.xls"
LastRow = wbThisWB.Worksheets("Sheet1").Cells(Row.Count, 1).End(xlUp).Row
For I = 1 To LastRow
WSName = wbThisWB.Worksheets("Sheets1").Cells(I, 1)
If sheetExists(WSName, wbThisWB) Then
MsgBox "Sheet found:" & WSName
lRow = Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("C2", "M" & lRow).Copy
Workbooks.Open Filename:="\\Shenetapp01\itt viability and intervention\Assurance Work AY 17-18\AGR\Test\" & WSName & " 17-18 AGR.xlsx"
Sheets("EY 17-18 Starters").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Next I
End Sub
Function sheetExists(sheetToFinad As String, wbThisWB As Workbook) As Boolean
sheetExists = False
For Each Sheet In wbThisWB.Worksheets
If sheetToFind = Sheet.Name Then
sheetExists = True
Exit Function
End If
Next Sheet
End Function