Are these 200 pages in one pdf, or 200 pdf files? I am assuming the latter although you write that it is only one file.
You need to loop through each of the files in a sub that then calls the startAdobe sub. But somewhere you will also want to close the pdf files.
Let's assume that all the pdf files are in one folder. The following will do the trick. I have used some of your code, but not the paste part, as we can use Excel's paste for that.
Read throuh the code (plus comments) and see what it does and how it differs from yours
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> LoopThroughFiles()<br> <SPAN style="color:#00007F">Dim</SPAN> strFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> colFiles <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">New</SPAN> Collection<br> <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> rLog <SPAN style="color:#00007F">As</SPAN> Range, rOut <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#00007F">Dim</SPAN> wsLog <SPAN style="color:#00007F">As</SPAN> Worksheet, wsOutp <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br> strPath = "C:\TEMP\"<br> strFile = Dir(strPath)<br> <SPAN style="color:#007F00">' Make a log sheet</SPAN><br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> wsLog = Sheets("PdfProcessLog")<br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br> <SPAN style="color:#00007F">If</SPAN> wsLog <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> wsLog = ThisWorkbook.Sheets.Add(before:=Sheets(1))<br> wsLog.Name = "PdfProcessLog"<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> rLog = wsLog.Range("A1")<br> rLog.CurrentRegion.ClearContents<br> rLog.Value = "PDF files copied to sheets"<br> <br> <SPAN style="color:#007F00">' load all the files in a Collection</SPAN><br> <SPAN style="color:#00007F">While</SPAN> strFile <> ""<br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">StrComp</SPAN>(Right(strFile, 3), "pdf", vbTextCompare) = 0 <SPAN style="color:#00007F">Then</SPAN><br> colFiles.Add strFile<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> strFile = Dir<br> <SPAN style="color:#00007F">Wend</SPAN><br> <br> Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br> <br> <SPAN style="color:#007F00">'Loop through the pdf's stored in the collection</SPAN><br> <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> colFiles.Count<br> <SPAN style="color:#007F00">'List filenames in Column A of the log sheet</SPAN><br> rLog.Offset(i, 0).Value = colFiles(i)<br> strFile = Left(colFiles(i), Len(colFiles(i)) - 4)<br> <br> <SPAN style="color:#007F00">' Delete sheet with filename if exists</SPAN><br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> wsOutp = Sheets(strFile)<br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wsOutp <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> wsOutp.Delete<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#007F00">' (Re)Create the worksheet, give it the file name</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> wsOutp = ThisWorkbook.Sheets.Add(after:=wsLog)<br> wsOutp.Name = strFile<br> <br> <SPAN style="color:#007F00">' Now open the file, and copy contents</SPAN><br> OpenClosePDF colFiles(i), strPath<br> CopyStep wsOutp<br> <SPAN style="color:#00007F">Next</SPAN> i<br> <br> Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> OpenClosePDF(<SPAN style="color:#00007F">ByVal</SPAN> sAdobeFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> sPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br><br> <SPAN style="color:#00007F">Dim</SPAN> sAdobeApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> vStartAdobe <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br> <br> sAdobeApp = "C:\Program Files (x86)\Adobe\Acrobat 5.0\Reader\AcroRd32.exe"<br> sAdobeApp = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe"<br> vStartAdobe = Shell("" & sAdobeApp & " " & sPath & sAdobeFile & "", 1)<br> Application.Wait (Now + TimeValue("0:00:01"))<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CopyStep(wsOutp <SPAN style="color:#00007F">As</SPAN> Worksheet)<br><br> <SPAN style="color:#007F00">' select all & copy</SPAN><br> SendKeys "^a", <SPAN style="color:#00007F">True</SPAN><br> SendKeys "^c", <SPAN style="color:#00007F">True</SPAN><br> Application.Wait (Now + TimeValue("0:00:01"))<br> <SPAN style="color:#007F00">' Paste into the sheet from cell A1</SPAN><br> wsOutp.Paste Cells(1, 1)<br> <br> Application.Wait (Now + TimeValue("0:00:01"))<br> AppActivate "Adobe Reader"<br> <SPAN style="color:#007F00">' close Reader</SPAN><br> SendKeys "%{F4}", <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>