VBA to copy from one tab to another

[ma0101]

Hi

I have a file which contains 2 tabs. The first tab (called "data") has 6 columns and 3 rows on it.

Row Number Date Name Type Value VAT
1 1 01/04/2015 ABC Limited Invoice 2,400 400
2 2 10/04/2015 DEF Limited Invoice 1,200 200
3 3 01/05/2015 JKL Limited Invoice 2,100 350

I want to transpose this data onto the second tab (called "result") however the structure in tab 2 (called "result") should be as follows:

Row No Column A Column B Column C Column D Column E
1 1 01/04/2015 ABC Limited
2 Invoice 2,400 400
3 2 10/04/2015 DEF Limited
4 Invoice 1,200 200
5 3 01/05/2015 JKL Limited
6 Invoice 2,100 350

The data on the first tab (called "data") can be any number of rows hence dynamic.

Can someone please help with a VB code which can transpose data from the first tab (called "data") to the second tab (called "result") on the above format? I am using Excel 2010.

Any help would be appreciated.

Regards

[skywriter]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ma0101]

Image to assist with question

Untitled.png

[ma0101]

File as requested attached

Book.xlsx

[skywriter]

Here you go.

Please Login or Register  to view this content.

[ma0101]

Thank you for your help. Works perfectly. I have a follow on request and would appreciate your assistance please.

Is there a way that I can manually allocate cells in sheet 1 to sheet 2 in the VBA code instead of select first 3 copy and paste with offset? So something like sheet 1 column A needs to be pasted to sheet 2 column D and so on...

The reason I ask is that sheet 2 is a batch input sheet and will have blank columns for data that I don't need to upload.

Thanks in advance.

[ma0101]

I have attached a file for what the data looks like and how I would like for it to be transposed.

VBA File.xlsx

Regards