Importing JPG, JPEG, and PNG images from a folder to an Excel worksheet
I will summarize what I said before (and add a few new ones):


Use Option Explicit at the top of your module. It helps you to prevent mistakes and forces you to declare the variables
instead of Instr use Right as in If Right(strCompFilePath,4)=".jpg" 
rename counter and counter1 so it is clear what they are (row and column indices)
Declare insert as a Sub and not a Function.
instead of passing the row and column index to the insert function/sub and then using the ActiveSheet pass the cell as a Range to the insert function/sub. (Instead of Activesheet you can use cell.Worksheet to get the right sheet) 
possibly rename insert so you don't confuse it with Pictures.insert
add the option savewithfile := true to the Pictures.Insert method so the pictures will stay in the file even if you send it somewhere.
as it is now, you don't really need NoOfFiles and mainWorkBook 
instead of the FileSystemObject I would use Dir. See this for more on that.