title: Copy Column Data to New Worksheet and Loop through Columns
body: <p>I have an Excel spreadsheet with over 60 columns.  Each column contains data from a SharePoint survey.  The column headers are the actual survey questions that have been imported from a SharePoint data connection.  I'm trying to do several things here.  </p>
  
  <p><strong>First, I want to copy each column to a new worksheet.</strong> (The reason I need to do this is so that I can add the data in each column to a PowerPivot Data Model.  PowerPivot recognizes the entire worksheet as one table, so it won't let me select only "Column A" to add to the Data Model, it automatically adds the entire table with all 60 columns).</p>
  
  <p>Since I don't want to manually add 60 new sheets first before this, I'd like the code to copy each column to a newly created worksheet.  (the worksheets don't need to be named, I could do that manually, unless someone has an easy way to do this as well!)</p>
  
  <p>Then, I'd like the code to loop through each column performing the copy and paste to the newly created sheet.</p>
  
  <p>I've found some examples of the code here for several related topics, but I'm so new to VBA that I'm having a hard time putting it all together.  Thank you all so much for your time!</p>
  
  <p>I've tried the following which does copy it over, but I'm not sure how to add a new sheet and loop through the columns</p>
  
  <pre><code>Sub CopyColumnToNewSheet()
  
      Dim lastRow As Long
      lastRow = Sheets("Sheet1").Range("A" &amp; Rows.Count).End(xlUp).Row
      Sheets("Sheet2").Range("A1:A" &amp; lastRow).Value = Sheets("Sheet1").Range("A1:A" &amp; lastRow).Value
  
  End Sub
  </code></pre>