title: VBA automation to copy/paste from websites
body: <p>OK.  So here's my thought process of how this code should work.  I have a sheet that has a list of web addresses that I would like to use the for loop to iterate through.  For each web address I'd like to copy and paste the whole web page to a temporary sheet in the workbook.  After the page is pasted into Excel(2010) I would do some formatting.  Only copy and paste what I want from the temporary sheet to a master sheet.  The next link is then navigated in Internet Explorer, and like before the web page is pasted to the temp sheet and then what I need is appended to the master sheet.  I'm experiencing the following issues:</p>
  
  <p>1) No matter what worksheet I declare for the web page to paste to it always pastes to the first sheet.  This happens to be the sheet that I have my command button on, but that shouldn't make a difference.  Should it?</p>
  
  <p>2) The appending to the master sheet is not working.  When the macro is finished running the only records that appear to have been pasted to the master sheet is from the last web page.</p>
  
  <p>I'm thinking that maybe what I need to do is add a pause of a certain amount of time between selecting/copying/pasting the web page to Excel to fix issue #2.  Help with either of these issues would be greatly appreciated.</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  Dim IE As Object
  
  startTime = Now()
  
  Set IE = CreateObject("InternetExplorer.Application")
  
  For rows1 = 2 To 11
      For columns1 = 2 To 2
          strLink = ThisWorkbook.Sheets("links").Cells(rows1, columns1)
  
  With IE
      .Visible = True
      .Navigate strLink
  Do While IE.ReadyState &lt;&gt; 4
              DoEvents
  Loop
  
  IE.ExecWB 17, 0
  IE.ExecWB 12, 2
  
  ThisWorkbook.Sheets("temp").Paste Range("A1")
  
  End With
  
  'Copy/paste and format from temp to master sheet
  nextRow = ThisWorkbook.Sheets("master").Range("A1").End(xlDown).Row + 1
  ThisWorkbook.Sheets("temp").Range("A173:S247").Copy
  ThisWorkbook.Sheets("Master").Range("A" &amp; nextRow).PasteSpecial Paste:=xlPasteValues
  
  Next columns1
  Next rows1
  
  endTime = Now()
  
  MsgBox ("Done running.  It took from " &amp; startTime &amp; " to " &amp; endTime &amp; ".")
  
  
  End Sub  
  </code></pre>