Excel macro to copy data from Sheet1 row into Sheet2 column

**peleong** · 10-12-2011, 02:12 AM

Hi

Nice to meet you all here. I am a newbie in vb and also a new member to Excel Forum.
I have encountered some difficulties in creating macro in excel where I need to copy data from Sheet1 row A1:C3 into Sheet2 column A1:C3 as well. Which means Sheet1 is a table whereas Sheet2 is a form. The idea now is to copy the data horizontally(row) become vertically(column) in each wordsheet respectively. Attached is the sample showing the simple requirement. Appreciate helps from anyone. Thanks.

**JBeaucaire** · 10-12-2011, 02:15 AM

This is done so fast with a cut/paste, I'm not sure it's worth writing a macro for.

1) Highlight the data on sheet2 A2:C???? (???? can be as far down as your data goes)
2) Copy
3) Switch to Sheet1 and click on B1
4) Do an Edit > Paste Special > Transpose

That could be recorded into a reusable macro in seconds. Just select a range on Sheet2 that goes down far enough to cover your future needs, 100-200 rows, perhaps?

**peleong** · 10-12-2011, 02:25 AM

Hi

Appreciate your fast response. I understand that it can be done simply just copy and paste the records from Sheet1 to Sheet2. However, it required a button (eg: Display) in worksheet "Form" which it will auto populate the mass data from "Table" worksheet into 'Form" worksheet whereby the aligment will be from row to column/horizontally to vertically. (Sorry if I didn't stated clearly in the first place).
Thanks.

**JBeaucaire** · 10-12-2011, 02:42 AM

Based on your sample, I don't think I misunderstood. You can record the steps I've suggested into a macro in seconds, then attach that macro to a button. Your example workbook and followup post have not added information to indicate a transposing cut/paste won't work just fine, even in a macro form.

**peleong** · 10-12-2011, 04:26 AM

Hi

Thanks for you patience and further guidance. I got your information. =)
However, I encountered another 2 issues:
i) When I assigned macro to the "Display Records" button, once I click on it, the alignment of the data populated in the form seems has not been transposed.
ii) When I recording the macro, I have left some spaces for the button on top of the form so that the record populated is at the next subsequent rows. However, when I assigned the macro to the button and click on the button for testing, the data populated seems started from A1 onwards automatically.

Appreciate your help and looking forward hearing from you soon. Thanks.

**peleong** · 10-12-2011, 04:39 AM

Sorry, please see the attachment of what I have done and it shows the matters that I concerned. Thanks.

**JBeaucaire** · 10-12-2011, 10:56 AM

The macro you recorded (?) does not appear to have followed the instructions post #2 at all. I don't see where you activated the Paste Special function anywhere. I recorded one for you, but that, unfortunately, doesn't really teach you to do what I was trying to show you. You should keep trying to ensure you find and get familiar with the Paste Special options.

I also returned this sheet back to its original format, then tweaked the recorded macro to:

1) always get the number of rows from the "table" that you've filled out, regardless of how many
2) always paste on the Form in the next empty column, wherever that may be

So, try clicking the button more than once...