Copy columns to rows????

[Ismael]

I you guys,

I think this is a easy question for you,

So imagine that I have several values in column b (something like this range B5:B30), now I want to paste this values to one row it could be for example C5:AF5.

Can you guys please tell me if exist any function that really doest that.

Thanks

Best regards,

Ismael

[Jacob Hilderbrand]

If you want to do this manually try this.

• Select the range of data (B5:B30)

• Edit | Copy

• Select the first cell of where you want to paste to (C5)

• Edit | PasteSpecial | Transpose

[Ismael]

I could do that manually, but I would like to do in automatic way, I don't now if this is possible to do.

thanks

[Jacob Hilderbrand]

Do you always want to copy from B5 down (for all the data in B) and paste to C5?

If so try this macro.

Option Explicit
 
Sub CopyAndPaste()
Dim LastRow         As Long
LastRow = Range("B65536").End(xlUp).Row
    Range("B5:B" & LastRow).Copy
    Range("C5").PasteSpecial Transpose:=True
End Sub

[Ismael]

yes for now it is always from B5 down and then paste to C5

[Jacob Hilderbrand]

Ok, the macro I posted above should work fine for you. You can then assign it to a Command Button or keyboard shortcut so you can easily run it whenever you want.

[Ismael]

Hi,

Wen I execute the macro a message of an error is return.

I want to send you the picture that contain the error but I don't now how.

[Jacob Hilderbrand]

Click Post Reply then scroll down to where it says Manage Attachments. You can post a pic, or just tell me what the error message is.

[Ismael]

ok, I will tell you what is the error because I don't now how I can create the URL for my Image.

The error is

" the information cannot be paste because the copy area and the paste area

are not the same size and shape. Try one of the following:

• click a single cell and then paste

• select a rectangle that have the same size and shape and then paste "

I hope this help

[Jacob Hilderbrand]

Make sure that the cells in question are not Merged.

• Select the cells

• Format | Cells... | Alignment

• Uncheck Merge cells

• Ok

[Ismael]

The cell aren't merge I think is because EXCEL doesn't transpose empty cell, so for example if we have a range in the macro for entire column b, but the values stop at b500 excel will return an error, a said that because I try to do the same thing manually now an EXCEL return the same message.

But now I have another problem, when I asked the question I forget one thing that are:

instead of values in one column I have values in 2 column for example column A and B, and what I want to do is

if in A2 is hjk and B2 is 2, and A3 is ert and b3 is 5, I want to write in

c5 hjk, d5 2, e5 ert and f5 5.

I think that will complicate a little bit the problem right?

[Jacob Hilderbrand]

You cannot Transpose 500 Rows of data because there are only 256 Columns available.

Is this what you are trying to do?

Option Explicit
 
Sub TransferData()
If Range("A2").Text = "hjk" And _
Range("B2").Value = 2 And _
Range("A3").Text = "ert" And _
Range("B3").Value = 5 Then
Range("C5").Value = "hjk"
Range("D5").Value = 2
Range("E5").Value = "ert"
Range("F5").Value = 5
End Sub

Now do you need to check through several rows like this:
Check A2:B3
Then Check A4:B5
Then Check A6:B7
Etc...

[Ismael]

I will not have more then 200 rows of data, the code that you done it's exactly what I want, the only problem is that the text in column A and B change the file to file isn't possible to do this in a way that EXCEl recognize automatic what are the text and then make the transpose.

thanks

Ismael

[Jacob Hilderbrand]

I'm sorry, I don't fully understand what you want to do. Can you make up an example workbook and put some data in it, then also put how you want the data to be setup after the macro runs?

You will need to Zip the workbook then you can attach it to your post.

Click on Post Reply then on the new page scroll down to Manage Attachments.

[Ismael]

ok, I have attach the file were you can see my doubt, I hope that hepls

Thanks

Ismael

[Ismael]

Hi DRJ,

Sorry to bother you again but I? am really stuck in this moment, and I would like to now if exist nay possibility to the thing that I ask you,

so if you have any free time please tell me something.

Once again, sorry the trouble.

best regards,

Ismael

[Jacob Hilderbrand]

Remember that you are limited in the number of columns. Since you are using two cells of data for each row and since you start at Column E you only can run this macro for 121 rows of data. After that you could go to a new row if you wanted.

Option Explicit

Sub Macro1()
Dim i               As Long
Dim LastRow         As Long
Application.EnableEvents = False
    Application.ScreenUpdating = False
'Starting with Column E so there is a 252 Column Limit
'Two Columns are used per Row of data so there is a 126 Row Limit
LastRow = Range("B65536").End(xlUp).Row
    If LastRow > 130 Then
        LastRow = 130
    End If
    Range("D5").Select
    For i = 5 To LastRow
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Range("B" & i).Text
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Range("C" & i).Value
    Next i
Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[Ismael]

Hi DRJ,

You really have given me a precious help, many thanks for you.

Best regards,

Ismael

[Jacob Hilderbrand]

You're Welcome

Take Care