i think this is what you want from point 2
Sub CopyTranspose()
Dim ws1 As Worksheet 'for summary worksheet
Dim wk As Worksheet 'for worksheet loop
Dim i As Long 'counter for current row
Set ws1 = Worksheets("Summary")
'clear summary
ws1.Cells.Clear
'reset first row
i = 2
For Each wk In Worksheets 'loop thru every worksheet in workbook
If wk.Name <> "Summary" Then 'skip worksheet "Summary"
If Application.CountA(wk.Range("B2:BY13")) > 0 Then 'if range is completely blank...if one cell has data it will copy all the blanks along with it
wk.Range("B2:BY13").Copy 'range copy
ws1.Cells(i, 1).PasteSpecial Paste:=xlFormats, Transpose:=True 'paste special formats
ws1.Cells(i, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True 'pastespecial values and number formats and transpose
i = ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1 'determine next row based on column A of summary
End If
End If
Next
End Sub
this is the only line i changed
i = ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1 'determine next row based on column A of summary
Bookmarks