0

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
braX
  • 11,506
  • 5
  • 20
  • 33
Barmy
  • 1
  • 1

0 Answers0