VBA Codes to copy, transpose and paste data

August 26, 2017 at 11:29:02
Specs: Windows 7

I have a worksheet containing data in the following format:
Header 1 and 2 are displayed in cell B1, C1,.,,,,Z1, B2, C2,...Z2 and date in A3, A4... and sales figures in row 3 onwards

	Type 1	Type 1	Type 1	Type 2	Type 2	Type 3
	Product A	Product B	Product C	Product M	Product N	Product O
2000	1	2	4	5	2	1
2001	2	4	5	2	6	7
2002	3	6	6	1	8	9
2003	4	8	9	4	12	3
2004	5	10	19	4	15	2
2005	6	12	21	7	7	6

In the 2nd worksheet (named as final format), I have preset the header as:
Date	Sales	Type	Product	Country

I would like to copy the date and sales figures from worksheet 1 and paste as values into Column A and B.
And to copy the headers (Type 1 and Product A), transpose and paste them as values into Column C and D. Column E (Country) is to be blank. A sample of the final required format is as follows:
Date	Sales	Type	Product	Country
2000	1	Type 1	Product A	
2001	2	Type 1	Product A	
2002	3	Type 1	Product A	
2003	4	Type 1	Product A	
2004	5	Type 1	Product A	
2005	6	Type 1	Product A	
2000	2	Type 1	Product B	
2001	4	Type 1	Product B	
2002	6	Type 1	Product B	
2003	8	Type 1	Product B	
2004	10	Type 1	Product B	
2005	12	Type 1	Product B	

Can I have the VBA codes to perform the above? Thanks.

message edited by lhm

See More: VBA Codes to copy, transpose and paste data

August 26, 2017 at 19:37:33
I don't think your data is displayed correctly and I don't want to assume anything about how it should be.

Please either correct the layout or confirm that it is correct.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Report •

August 26, 2017 at 23:09:27
Hi, Thanks for the reply. The format is correct. I would like to provide additional information on 2 worksheets.
1st worksheet: the first 2 rows contains the header 1 and header 2 and row 3 (Col A) contains the date which is not fixed, it would extend to 2006, 2007, etc. Sales figures are indicated in Col B onwards (no. of columns across is not fixed). The no. of datapoints might vary across the columns as well.
2nd worksheet: The first row is the pre-set headers.
To copy data containing date and sales figures (cell A3 to B8) from worksheet 1. Paste as values into Col A and B (cell A2 to B7) into worksheet2 below the preset headers. To copy the corresponding headers from Worksheet 1 containing headers in cell B1 and B2 (Type 1 and Product A) and transpose and paste as values into cell C2 and D2.
From the sample table above, after this step, in row 2, the data are displayed as Date (2001), Sales (1), Type (Type 1) and Product (Product A) and Country (blank). For row 3 to 7, it only contains the Date (2001,..2005) and Sales (2,..6) but the corresponding Type and Product are empty. Hence, I would need an additional step to copy the data on Type (Type 1) and Product (Product A) from C2 to D2 and paste below in C3 to D7. In general, the data on Type and Product for the 1st record should apply to the remaining years. Sorry for missing out this information in my earlier request. This forms block 1.
For the next block, to copy date and sales figures from A3 to A8 and cell C3 to C8 of wksheet1 and paste below block 1 (such that it appears in cell A8 to B13). Copy headers in cell C1 and C2 (Type 1 and Product B) of wksheet1, tranpose and paste as values into C8 to D8. To copy and paste Type 1 and Product B for the rest of the dates below (C9 to D13). This forms block 2.
The above steps would repeat till the last available column of worksheet 1.
Please note that the no, of periods, columns, row are not fixed, hence the cell references are indicated for illustration only. Hence, I would need the VBA codes to copy the available columns and rows from worksheet 1 and paste in the required format into worksheet2.
Please kindly advise. Thanks for your help.

message edited by lhm

Report •

August 27, 2017 at 09:01:20
re: "The format is correct."

I hate to be a pain, but are you sure? Since you did not include column letters or row numbers, it's hard to tell how your columns are supposed to be lined up.

I am seeing the 6 Type x headings overlapping the Product A, B and C headings. 6 Type headings in one row overlapping only 3 Product headings in the next row.

I am seeing a column containing 1 - 6 and a column containing 2 - 12 under the heading Product A, yet your output seems to indicate that 2 - 12 should be under the heading for Product B.

I am seeing basically the same thing under the Product B & C headings: 2 columns of numbers under each heading. Unless your Product headings are merged cells and are supposed to have 2 columns of numbers under each Product heading, something is wrong.

In other words, you appear to have 6 Products (A, B, C, M, N, O) and 6 columns of numbers, but the 6 columns of numbers are all under the headings for A, B and C, with 2 columns under each.

At a minimum, please add column letters and row numbers so that it is clear to those of us sitting way out here on the internet how your sheet is set up.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Report •
Related Solutions

Ask Question