VBA codes to extract data into different worksheets

September 11, 2016 at 00:29:16
Specs: Windows 7
Hi, 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.

	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

I have issue displaying table properly using Pre tag. Very sorry for this.
You would be able to see the correct format if you copy the table and
paste in excel->text to column->Delimited by space. I shall describe the
table in details. For example, Product type A is displayed from Col B to
G. Row 3 contained the date and row 4 to 8 contained the data for
Product1 to 5. Column H to M contained data for Product type B,
etc.......

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.

I would like to 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!1B35/10. The cell references are the same for the 2 worksheets.
Cells are formatted to display figures in 1 decimal place.

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.

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

Thanks for your advice and assistance in advance.


See More: VBA codes to extract data into different worksheets

Reply ↓  Report •

Related Solutions


Ask Question