Transposing columns and known rows multiple times

Hello all,

Always come here first to find what I am looking for and usually able to find something that I can amend.
However, this time I am not quite able to find what I need, I think this is due to not quite knowing how to search for it.

I have found lots of examples on how to transpose from one worksheet to another and this usually just involves one column.

Now to my issue,

On sheet 1 I have date in columns A - Z and any number of rows.
What is constant is that the rows repeat after 25 rows.

I want to be able to copy from rows 1 - 25 and columns A-Z and transpose to Sheet 2
Then copy the next 25 rows and columns A-Z and paste on Sheet 2 below the first range of data.
Continue of down Sheet1 transposing every 25 rows to Sheet 2 until end of data.

Below is how it looks if I record a macro for the first 2 sections I want to transpose and this would continue to the end of all used rows.

Any help would be great.

Am unable to upload example sheets due to firewall at work.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:Z25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("Sheet1").Select
    Range("A26:V50").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A23").Select
    ActiveSheet.Paste
    Range("A48").Select
End Sub

Sub columnstocol()
 Dim i As Long, ms As Worksheet, LR&
 
  Application.ScreenUpdating = 0
  
  Set ms = Sheets("Sheet2")
 
  With Sheets("Sheet1")
  
    LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    
    For i = 1 To LR Step 25

      .Cells(i, 1).Resize(25, 26).Copy ms.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(25, 26)

    Next
    
    Application.CutCopyMode = 0
    Application.ScreenUpdating = True
  End With
     ms.Activate
    Set ms = Nothing
End Sub

Hi AB33,

That was a quick reply!!
Thanks for having a look at my issue.
I have just tested the code and its not quite working as I want.
There seems to be the copy, transpose part missing from your code.

As I want the data copied from sheet 1 and transposed to sheet 2 so that the rows become columns.

Thanks for your time

Sub columnstocoll()
 Dim i As Long, ms As Worksheet, LR&
 
  Application.ScreenUpdating = 0
  
  Set ms = Sheets("Sheet2")
 
  With Sheets("Sheet1")
  
    LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    
    For i = 1 To LR Step 25
    ms.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 25) = Application.Transpose(.Cells(i, 1).Resize(25))
    Next
    
    Application.CutCopyMode = 0
    Application.ScreenUpdating = True
  End With
     ms.Activate
    Set ms = Nothing
End Sub

Hi AB33,

Almost there, perhaps I didnt explain the situation as clear as possible.

My data looks similar to this


----A --------B -------C ------ D - z
1 CINAme abc def abf and so on to Z
2 SerNo 1234 2345 3456 and so on to z

This continues down to row 25 with other headers and information

I want to then transpose ALL columns from A- Z and rows 1 to 25 to Sheet 2

so end results now looks like this

--- A---- -----B------C----- to z
1 CIName SrNo Location and so on
2 abc 123 hhhh
3 def 234


Does that make sense.
The last code you sent over , did pretty much that but only with Column A
I tried to change the range to include A:Z and A1:Z100 but that didnt work.

Could you please attach your sample as I could not see the output in text format. Please attach one sheet with raw data and another sheet(With in the same book), an output sheet.

Hi,

Here is an example just filling in with some data and not the actual used data.

Sheet1 is the initial data and the data goes across the columns
Sheet2 is how I want to transpose the data.

Thanks for your time

Try the attached. It works on the sample, but not sure if it works on your real data. Will carry on tomorrow.

Originally Posted by AB33

Try the attached. It works on the sample, but not sure if it works on your real data. Will carry on tomorrow.

Hello and good morning.

Just tried the example code that you sent over and it works perfectly.
That's great and just what I wanted it to do.

Thank you very much for all the time and effort you spent on getting my bed explanation into something that works.

I really appreciate it.

Have a great day!!

Hi AB33

Can I get some help in transposing multiple columns to multiple rows (and lesser # of columns)?

Thanks

Ravi

aprcamb,
Glad to know it has worked for you! I am thrilled too and You are welcome!

Ravi,
Welcome to the forum!
Please start your own thread.
It is against forum's rule to post on another person's thread.
To get a speed reply, post a sample book with a desired result where one sheet shows your raw data and another sheet a desired result.