title: Macro to copy and transpose every row and past in Cell Q1 Column
body: <p>I have village names in column A.as below mentioned format</p>
  
  <pre><code>VILLAGE
  Campbelbay
  Carnicobar
  Champin
  Chowra
  Gandhinagar
  Kakana
  Kapanga
  </code></pre>
  
  <p>With this format I have around 700 sheets in workbook. I need to get the same transposed to the below mentioned format in Column(cell) Q1.</p>
  
  <pre><code>Campbelbay,Carnicobar,Champin,Chowra,Gandhinagar,Kakana,Kapanga
  </code></pre>
  
  <p>I have a macro code works for 8 cells and for one sheet, can somebody help me to apply this macro to all sheets with auto select row number.? i.e, Sheets1 has 30 rows, sheet2 has 50 rows and sheet n has n rows.</p>
  
  <p>I do not have much of knowledge in VB.</p>
  
  <p>Following is the code that works for Sheet1:
  Ref:</p>
  
  <p><a href="https://stackoverflow.com/questions/1965600/macro-to-copy-and-transpose-every-seventh-row-and-past-in-new-sheet">macro to copy and transpose every seventh row and past in new sheet</a></p>
  
  <pre><code>Public Sub TransposeData()
      Dim LastRow As Long
      Dim NextRow As Long
      Dim i As Long
  
      Application.ScreenUpdating = False
  
      With Worksheets("Sheet1")
          LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  
          For i = 1 To LastRow Step 8
              .Cells(i, "A").Resize(8).Copy
              NextRow = NextRow + 1
              .Cells(NextRow, "B").PasteSpecial Paste:=xlPasteAll, transpose:=True
          Next i
  
          .Rows(NextRow + 1).Resize(LastRow - NextRow).Delete
          .Columns(1).Delete
      End With
  
      Application.ScreenUpdating = True
  End Sub
  </code></pre>