title: VBA to copy and paste transpose within different workbooks
body: <p>I have 200 different workbook. I want to copy and paste transpose from 1 sheet into another worksheet. Is there a VBA code for that?</p>
  
  <p>For example: 
  Workbook 1 now has 1 worksheet called worksheet A.</p>
  
  <p>I want to copy, paste transpose worksheet A into worksheet 1, within workbook 1.</p>
  
  <p>Is there a code that works for workbook 1-200?</p>
  
  <pre><code>Sub Macro2() 
      Dim thiswb As Workbook, datawb As Workbook 
      Dim datafolder As String 
      Dim cell As Range, datawblist As Range 
      Dim i As Integer 
  
      Set thiswb = ActiveWorkbook 
      i = 2 'Have the 40 file names in sheet2 of this workbook in cells A1:A40 
      Set datawblist = Sheets("sheet2").Range("A1:A40") 
      Sheets("sheet1").Cells(i, 1).Select 
      datafolder = "H:\TestData\" 'change this to your directory they're in 
  
      For Each cell In datawblist 
          Workbooks.Open Filename:=datafolder &amp; cell &amp; ".xlsx", ReadOnly:=True 
          Set datawb = ActiveWorkbook
          Sheets("sheet1").Select 'change this to the sheet name you need to copy from 
          Range("C3").Select 
  
          Do Until ActiveCell.Value = "" 
              Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(38, ActiveCell.Column)).Copy 
              thiswb.Activate
              Sheets("Sheet1").Select 'change if not sheet1 
              Selection.PasteSpecial Paste:=xlPasteValues, _ 
                                     Operation:=xlNone, _
                                     SkipBlanks:=False, _
                                     Transpose:=True 
              ActiveCell.Offset(0, 36).Select 
              datawb.Activate 
              ActiveCell.Offset(0, 1).Select 
          Loop 
  
          datawb.Close savechanges:=False 
          thiswb.Activate
          Sheets("sheet1").Select 
          i = i + 1
          Cells(i, 1).Select 
      Next 
  End Sub
  </code></pre>