VBA codes to extract data into different worksheets

April 30, 2017 at 05:49:11
Specs: Windows 7
I have a few datasets.
Dataset1 is in the format in an excel file (file 1) below as of Mar2016. There are 3 main product types
(A, B and C) and each product type is categorized into 5 products with data from 2015/January to
2016/March.

The headers for the product types (e.g. type A in B2 to G2) are merged cells.

Full dataset 1 (original format)

	TypeA						TypeB						TypeC					
Amount	2015/January	……..	2015/December	2016/January	2016/February	2016/March	2015/January	……..	2015/December	2016/January	2016/February	2016/March	2015/January	……..	2015/December	2016/January	2016/February	2016/March
Product1	11	16	21	10	15	20	14	25	16	13	24	15	3	6	75	2	5	74
Product2	4	6	8	3	5	7	11	46	13	10	45	12	57	2	4	56	1	3
Product3	2	3	6	1	2	5	37	13	8	36	12	7	11	16	28	10	15	27
Product4	6	9	10	5	8	9	11	46	6	10	45	5	46	24	13	45	23	12
Product5	11	26	8	10	25	7	16	24	13	15	23	12	42	11	16	41	10	15

As of Apr2016, I would have another new file (file 2) and the dataset would contain data from
2015/January to 2016/April (with 3 additional columns added for the current month 2016/April
data for the 3 product types)

Every month, there would be a new file, containing additional columns for the current month.

Can I have the VBA codes to extract and process the data in the following steps automatically?

1. To extract the data for certain periods only (e.g. from January2016 to March2016) and paste into
a new blank file (in worksheet 1) automatically

Final format (worksheet 1)

	TypeA			TypeB			TypeC		
Amount	2016/January	2016/February	2016/March	2016/January	2016/February	2016/March	2016/January	2016/February	2016/March
Product1	10	15	20	13	24	15	2	5	74
Product2	3	5	7	10	45	12	56	1	3
Product3	1	2	5	36	12	7	10	15	27
Product4	5	8	9	10	45	5	45	23	12
Product5	10	25	7	15	23	12	41	10	15

2. After the data are copied into worksheet1, the same table is duplicated in worksheet2 but without
data. Set formula such the value in worksheet2 is equal to the value in worksheet1 divided by 10, e.g.
Sheet!1B2/10. The cell references are the same for the 2 worksheets.

Final format (worksheet 2)

	TypeA			TypeB			TypeC		
Amount	2016/January	2016/February	2016/March	2016/January	2016/February	2016/March	2016/January	2016/February	2016/March
Product1	1	1.5	2	1.3	2.4	1.5	0.2	0.5	7.4
Product2	0.3	0.5	0.7	1	4.5	1.2	5.6	0.1	0.3
Product3	0.1	0.2	0.5	3.6	1.2	0.7	1	1.5	2.7
Product4	0.5	0.8	0.9	1	4.5	0.5	4.5	2.3	1.2
Product5	1	2.5	0.7	1.5	2.3	1.2	4.1	1	1.5

3. To duplicate worksheet 2 in worksheet 3 without formula (copy and paste the data as values)

Cells are formatted to display figures in 1 decimal place.

Final format (worksheet 3)

	TypeA			TypeB			TypeC		
Amount	2016/January	2016/February	2016/March	2016/January	2016/February	2016/March	2016/January	2016/February	2016/March
Product1	1	1.5	2	1.3	2.4	1.5	0.2	0.5	7.4
Product2	0.3	0.5	0.7	1	4.5	1.2	5.6	0.1	0.3
Product3	0.1	0.2	0.5	3.6	1.2	0.7	1	1.5	2.7
Product4	0.5	0.8	0.9	1	4.5	0.5	4.5	2.3	1.2
Product5	1	2.5	0.7	1.5	2.3	1.2	4.1	1	1.5

Every month, there would be a new file, containing additional columns for the current month. The
VBA codes should work for rest of the months and there is no need to write a new set of codes for
each month.

As I have a few datasets with quite similar format (slight variations) as Dataset1, preferably I hope
that the VBA codes can allow flexibility in selecting any starting date and end date and any product
number or even any Product type to extract the data, if technically feasible.

Or otherwise, I am fine if different set of codes have to be customised and used for each datasets.

Thank you for your help.

Dataset 2
Can I have the VBA codes to extract and process the data in the following steps automatically?

Full dataset 2 (original format)
The headers for the product categories (e.g. category X in A5 to A9) are merged cells.

Amount		TypeA					
		2015/January	……..	2015/December	2016/January	2016/February	2016/March
Cateory X	Product1	11	16	21	10	15	20
	Product2	4	6	8	3	5	7
	Product3	2	3	6	1	2	5
	Product4	6	9	10	5	8	9
	Product5	11	26	8	10	25	7
Cateory Y	Product1	11	16	21	10	15	20
	Product2	4	6	8	3	5	7
	Product3	2	3	6	1	2	5
	Product4	6	9	10	5	8	9
	Product5	11	26	8	10	25	7
Cateory Z	Product1	11	16	21	10	15	20
	Product2	4	6	8	3	5	7
	Product3	2	3	6	1	2	5
	Product4	6	9	10	5	8	9
	Product5	11	26	8	10	25	7

1. To extract the data for certain periods only (e.g. from January2016 to March2016) and paste into a
new blank file (in worksheet 1) automatically. Formatting shall remain (colour of text, highlights, font
size, etc….)

Final format (worksheet 1)

Amount				
		2016/January	2016/February	2016/March
Cateory X	Product1	10	15	20
	Product2	3	5	7
	Product3	1	2	5
	Product4	5	8	9
	Product5	10	25	7
Cateory Y	Product1	20	25	30
	Product2	13	15	17
	Product3	11	12	15
	Product4	15	18	19
	Product5	20	35	17
Cateory Z	Product1	30	35	40
	Product2	23	25	27
	Product3	21	22	25
	Product4	25	28	29
	Product5	30	45	27

2. After the data are copied into worksheet1, the same table is duplicated in worksheet2 but without
data. Set formula such the value in worksheet2 is equal to the value in worksheet1 divided by 10, e.g.
Sheet!1B3/10. The cell references are the same for the 2 worksheets.

In worksheet1, data is categorized by Category in Col A and then by Product type in Col B. Data are
arranged according to ascending order of each Category and then list of Products. In 2nd worksheet, to
copy the data from worksheet1 such that data are arranged according to each Product and then list of
Category.

Final format (worksheet 2)

Amount				
		2016/January	2016/February	2016/March
Cateory X	Product1	10	15	20
Cateory Y	Product1	20	25	30
Cateory Z	Product1	30	35	40
Cateory X	Product2	3	5	7
Cateory Y	Product2	13	15	17
Cateory Z	Product2	23	25	27
….	…..	…..	…..	….
Cateory X	Product5	10	25	7
Cateory Y	Product5	20	35	17
Cateory Z	Product5	30	45	27		

3. After the data are copied into worksheet2, the same table is duplicated in worksheet3 but without
data. Set formula such the value in worksheet3 is equal to the value in worksheet2 divided by 10 and
round to 0 decimal place, e.g. round(Sheet!1C31/10,0). The cell references are the same for the 2
worksheets.

Cells are formatted to display figures in 0 decimal place.

Final format (worksheet 3 & 4)

Amount				
		2016/January	2016/February	2016/March
Cateory X	Product1	1	2	2
Cateory Y	Product1	2	3	3
Cateory Z	Product1	3	4	4
Cateory X	Product2	0	1	1
Cateory Y	Product2	1	2	2
Cateory Z	Product2	2	3	3
….	…..	….	…..	…..
Cateory X	Product5	1	3	1
Cateory Y	Product5	2	4	2
Cateory Z	Product5	3	5	3

3. To duplicate worksheet 3 in worksheet 4 without formula (copy and paste the data as values). Cells
are formatted to display figures in 0 decimal place.

4. To copy entire worksheet 4 and paste as values into worksheet 5.

Final format (worksheet 5)				
				
Amount				
		2016/January	2016/February	2016/March
Cateory X	Product1	1	2	2
Cateory Y	Product1	2	3	3
Cateory Z	Product1	3	4	4
Cateory X	Product2	0	1	1
Cateory Y	Product2	1	2	2
Cateory Z	Product2	2	3	3
….	…..	….	…..	…..
Cateory X	Product5	1	3	1
Cateory Y	Product5	2	4	2
Cateory Z	Product5	3	5	3

5. To copy and paste each of the Product types into next few worksheets and rename the worskheet
name accordingly.

If the Product name is too long to fit into worksheet name, to shorten the name based on the max no.
of characters (e.g first few 100 characters).

Final format (worksheet 6)-> worksheet name is Product1

Amount				
		2016/January	2016/February	2016/March
Cateory X	Product1	1	2	2
Cateory Y	Product1	2	3	3
Cateory Z	Product1	3	4	4

Final format (worksheet 10)-> worksheet name is Product5

Amount				
		2016/January	2016/February	2016/March
Cateory X	Product5	1	3	1
Cateory Y	Product5	2	4	2
Cateory Z	Product5	3	5	3


See More: VBA codes to extract data into different worksheets

Report •

#1
May 3, 2017 at 01:27:00
So what is your question? are you asking for help with this? if so where have you asked?

How far have you gotten with it? this is very much doable but A LOT of work is needed and I don't think anyone will be willing to recreate the workbook. It may be worth sending your workbook, PM me for the email address to send it to.


Report •
Related Solutions


Ask Question