• 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

    Hui...

  • 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

Dee

Member
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

Member
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

New Member
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

Member
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

Excel Ninja
Staff member
Dee

Your 2 lines


Set Thiswbook = Workbooks(Filewithmacro)


ThisWorkbook.Worksheets("Project Managers").Activate


Shouldn't the second line be

Thiswbook.Worksheets("Project Managers").Activate


?
 
Top