Error in macro - Index refers

[Dee]

Dear All,


I am getting the following error "Index refers beyond end of list" in the row presented within astricks (***)when i try to run this macro. I have two tabs called 'Project Managers' & 'Project List' in the file and at present i have only 4 names in Project Managers tab. The will grow in future.


Sub getprojects()


Dim Ftwbook As Workbook

Dim Thiswbook As Workbook

Dim LastPM, LastCell, RIndex, counter As Integer

Dim Wksheet As Worksheet


Dim Flatfilename As String

Dim Filewithmacro As String


Application.ScreenUpdating = False


Flatfilename = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", Title:="Please choose a file")

Filewithmacro = ThisWorkbook.Name


Set Ftwbook = Workbooks.Open(Filename:=Flatfilename, ReadOnly:=yes)

Set Thiswbook = Workbooks(Filewithmacro)


ThisWorkbook.Worksheets("Project Managers").Activate

Range("A1").Select


LastCell = ActiveCell.SpecialCells(xlCellTypeLastCell).Row


ReDim PM(LastCell) As String


For counter = 0 To LastCell - 1

PM(counter) = ActiveCell.Value

ActiveCell.Offset(1, 0).Activate

Next counter


Thiswbook.Worksheets("Project List").Activate

Range("A4", Range("A4").SpecialCells(xlCellTypeLastCell).Address).Rows.ClearContents


Ftwbook.Activate

Ftwbook.Worksheets("Flat File").Select

Range("Z4").Select

LastPM = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

RIndex = 4


For counter = 0 To LastPM - 1


For Index = 0 To LastCell - 1


If ActiveCell.Value = PM(Index) Then

ActiveCell.EntireRow.Copy

Thiswbook.Worksheets("Project List").Activate

ActiveSheet.Paste Destination:=Range(Cells(RIndex, 1), Cells(RIndex, 1))

Ftwbook.Worksheets("Flat File").Activate

RIndex = RIndex + 1

Exit For

Else

End If

Next Index


ActiveCell.Offset(1, 0).Activate


Next counter


Application.CutCopyMode = False

Ftwbook.Close SaveChanges = no

Application.ScreenUpdating = True


'Call LastRowofPM


End Sub


Thanking you in advacne

 

[Dee]

Sorry i have not highlighted the error row

The error i am getting is in 13th row ie., ***ThisWorkbook.Worksheets("Project Managers").Activate***


Thanks,

 

[TessaES]

Dee,


To me, that error sounds like the worksheet is not present in the workbook you refer to.


Just before the line with the error, you define names for two workbooks: Ftwbook and Thiswbook. But on the line with the error you refer to ThisWorkbook. Are you referring to the correct workbook?


Also, check the worksheet name for any typing errors and leading or trailing spaces.

 

[Dee]

Thanks for this.

But i tried all these things but still i am not getting the reason why i am getting this error


Dee...

 

[Hui]

Dee

Your 2 lines


Set Thiswbook = Workbooks(Filewithmacro)


ThisWorkbook.Worksheets("Project Managers").Activate


Shouldn't the second line be

Thiswbook.Worksheets("Project Managers").Activate


?