PDA

View Full Version : [SOLVED] Copy columns to rows????



Ismael
02-04-2005, 02:57 AM
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
02-04-2005, 03:04 AM
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
02-04-2005, 03:09 AM
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
02-04-2005, 03:13 AM
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
02-04-2005, 03:20 AM
yes for now it is always from B5 down and then paste to C5

Jacob Hilderbrand
02-04-2005, 03:21 AM
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
02-04-2005, 03:41 AM
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
02-04-2005, 03:42 AM
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
02-04-2005, 04:09 AM
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
02-04-2005, 04:13 AM
Make sure that the cells in question are not Merged.

Select the cells


Format | Cells... | Alignment


Uncheck Merge cells

Ok

Ismael
02-04-2005, 04:22 AM
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
02-04-2005, 04:27 AM
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
02-04-2005, 04:37 AM
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
02-04-2005, 04:43 AM
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
02-04-2005, 04:53 AM
ok, I have attach the file were you can see my doubt, I hope that hepls

Thanks

Ismael

Ismael
02-04-2005, 06:57 AM
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
02-04-2005, 07:39 AM
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
02-04-2005, 07:48 AM
Hi DRJ,

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

Best regards,

Ismael


:thumb

Jacob Hilderbrand
02-04-2005, 07:54 AM
You're Welcome :beerchug:

Take Care