• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error in macro - Index refers


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


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.Worksheets("Flat File").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


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


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


Sorry i have not highlighted the error row

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



New Member

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.


Thanks for this.

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



Excel Ninja
Staff member

Your 2 lines

Set Thiswbook = Workbooks(Filewithmacro)

ThisWorkbook.Worksheets("Project Managers").Activate

Shouldn't the second line be

Thiswbook.Worksheets("Project Managers").Activate