Require VBA Code

[loknathmondal]

There are 4 excel sheets in a workbook and every sheet has huge data i.e.
datewise productwise sales details


Now i want get the below details in 5th excel sheet i.e. total sales of
specific product in specific time by using VBA code as every sheet has atleast
4-5 lakhs data



Why i'm asking this as i have to select specific product then specific priod of
each excel sheet and paste it to new excel sheet, it took 30-45 minutes.

Example Below:

Products	Volume	Transaction Date
Mango	76	17-10-17
Apple	130	19-10-17
Orange	12	21-10-17
Banana	110	23-10-17
Guava	137	25-10-17
Cherry	117	27-10-17
Sweet	125	29-10-17
Papiya	45	31-10-17
Litchi	124	02-11-17
Jack Fruit	66	04-11-17
Lemon	105	06-11-17
Ginger	54	08-11-17
Garlic	30	10-11-17
Cumin	69	12-11-17
Mango	140	14-11-17
Apple	29	16-11-17
Orange	118	18-11-17
Banana	95	20-11-17
Guava	47	22-11-17
Cherry	126	24-11-17
Sweet	112	26-11-17
Papiya	35	28-11-17
Litchi	17	30-11-17
Jack Fruit	82	02-12-17
Lemon	81	04-12-17
Ginger	10	06-12-17
Garlic	93	08-12-17
Cumin	35	10-12-17
Mango	75	12-12-17
Apple	64	14-12-17
Orange	88	16-12-17
Banana	135	18-12-17
Guava	35	20-12-17
Cherry	84	22-12-17
Sweet	104	24-12-17
Papiya	72	26-12-17
Litchi	43	28-12-17
Jack Fruit	39	30-12-17
Lemon	68	01-01-18
Ginger	85	03-01-18
Garlic	66	05-01-18
Cumin	135	07-01-18
Mango	53	09-01-18
Apple	32	11-01-18
Orange	70	13-01-18
Banana	12	15-01-18
Guava	130	17-01-18
Cherry	127	19-01-18
Sweet	78	21-01-18
Papiya	52	23-01-18
Litchi	143	25-01-18
Jack Fruit	132	27-01-18
Lemon	93	29-01-18
Ginger	87	31-01-18
Garlic	31	02-02-18
Cumin	68	04-02-18
Mango	94	06-02-18
Apple	111	08-02-18
Orange	94	10-02-18
Banana	20	12-02-18
Guava	63	14-02-18
Cherry	45	16-02-18
Sweet	14	18-02-18
Papiya	121	20-02-18
Litchi	129	22-02-18
Jack Fruit	35	24-02-18
Lemon	29	26-02-18
Ginger	18	28-02-18
Garlic	132	02-03-18
Cumin	93	04-03-18
Mango	51	06-03-18
Apple	122	08-03-18
Orange	64	10-03-18
Banana	141	12-03-18
Guava	49	14-03-18
Cherry	43	16-03-18
Sweet	82	18-03-18
Papiya	59	20-03-18
Litchi	83	22-03-18
Jack Fruit	140	24-03-18
Lemon	25	26-03-18
Ginger	30	28-03-18
Garlic	65	30-03-18
Cumin	91	01-04-18
Mango	109	03-04-18
Apple	68	05-04-18
Orange	36	07-04-18
Banana	148	09-04-18
Guava	57	11-04-18
Cherry	14	13-04-18
Sweet	82	15-04-18
Papiya	110	17-04-18
Litchi	133	19-04-18
Jack Fruit	80	21-04-18
Lemon	51	23-04-18
Ginger	12	25-04-18
Garlic	36	27-04-18
Cumin	25	29-04-18
Mango	111	01-05-18
Apple	41	03-05-18
Orange	66	05-05-18
Banana	51	07-05-18
Guava	45	09-05-18
Cherry	84	11-05-18
Sweet	95	13-05-18
Papiya	105	15-05-18
Litchi	17	17-05-18
Jack Fruit	141	19-05-18
Lemon	52	21-05-18
Ginger	30	23-05-18
Garlic	52	25-05-18
Cumin	105	27-05-18
Mango	123	29-05-18
Apple	108	31-05-18
Orange	134	02-06-18
Banana	126	04-06-18
Guava	128	06-06-18
Cherry	110	08-06-18
Sweet	72	10-06-18
Papiya	71	12-06-18
Litchi	24	14-06-18
Jack Fruit	18	16-06-18
Lemon	45	18-06-18
Ginger	109	20-06-18
Garlic	20	22-06-18
Cumin	54	24-06-18
Mango	60	26-06-18
Apple	145	28-06-18
Orange	125	30-06-18
Banana	52	02-07-18
Guava	141	04-07-18
Cherry	144	06-07-18
Sweet	24	08-07-18
Papiya	96	10-07-18
Litchi	34	12-07-18
Jack Fruit	84	14-07-18
Lemon	48	16-07-18
Ginger	105	18-07-18
Garlic	42	20-07-18
Cumin	71	22-07-18
Mango	12	17-10-17
Apple	58	19-10-17
Orange	117	21-10-17
Banana	13	23-10-17
Guava	111	25-10-17
Cherry	71	27-10-17
Sweet	16	29-10-17
Papiya	14	31-10-17
Litchi	142	02-11-17
Jack Fruit	142	04-11-17
Lemon	43	06-11-17
Ginger	96	08-11-17
Garlic	67	10-11-17
Cumin	103	12-11-17
Mango	29	14-11-17
Apple	49	16-11-17
Orange	73	18-11-17
Banana	93	20-11-17
Guava	35	22-11-17
Cherry	127	24-11-17
Sweet	119	26-11-17
Papiya	73	28-11-17
Litchi	77	30-11-17
Jack Fruit	139	02-12-17
Lemon	126	04-12-17
Ginger	53	06-12-17
Garlic	19	08-12-17
Cumin	98	10-12-17
Mango	44	12-12-17
Apple	86	14-12-17
Orange	46	16-12-17
Banana	99	18-12-17
Guava	142	20-12-17
Cherry	64	22-12-17
Sweet	71	24-12-17
Papiya	130	26-12-17
Litchi	56	28-12-17
Jack Fruit	103	30-12-17
Lemon	122	01-01-18
Ginger	29	03-01-18
Garlic	11	05-01-18
Cumin	56	07-01-18
Mango	120	09-01-18
Apple	35	11-01-18
Orange	138	13-01-18
Banana	19	15-01-18
Guava	89	17-01-18
Cherry	134	19-01-18
Sweet	110	21-01-18
Papiya	27	23-01-18
Litchi	25	25-01-18
Jack Fruit	132	27-01-18
Lemon	137	29-01-18
Ginger	128	31-01-18
Garlic	60	02-02-18
Cumin	131	04-02-18
Mango	121	06-02-18
Apple	122	08-02-18
Orange	136	10-02-18
Banana	123	12-02-18
Guava	135	14-02-18
Cherry	89	16-02-18
Sweet	135	18-02-18
Papiya	115	20-02-18
Litchi	119	22-02-18
Jack Fruit	147	24-02-18
Lemon	18	26-02-18
Ginger	83	28-02-18
Garlic	40	02-03-18
Cumin	124	04-03-18
Mango	104	06-03-18
Apple	24	08-03-18
Orange	35	10-03-18
Banana	126	12-03-18
Guava	68	14-03-18
Cherry	63	16-03-18
Sweet	76	18-03-18
Papiya	58	20-03-18
Litchi	129	22-03-18
Jack Fruit	81	24-03-18
Lemon	93	26-03-18
Ginger	103	28-03-18
Garlic	150	30-03-18
Cumin	104	01-04-18
Mango	19	03-04-18
Apple	72	05-04-18
Orange	116	07-04-18
Banana	85	09-04-18
Guava	63	11-04-18
Cherry	74	13-04-18
Sweet	46	15-04-18
Papiya	103	17-04-18
Litchi	124	19-04-18
Jack Fruit	10	21-04-18
Lemon	87	23-04-18
Ginger	33	25-04-18
Garlic	91	27-04-18
Cumin	123	29-04-18
Mango	84	01-05-18
Apple	77	03-05-18
Orange	16	05-05-18
Banana	105	07-05-18
Guava	64	09-05-18
Cherry	50	11-05-18
Sweet	48	13-05-18
Papiya	66	15-05-18
Litchi	48	17-05-18
Jack Fruit	72	19-05-18
Lemon	147	21-05-18
Ginger	146	23-05-18
Garlic	11	25-05-18
Cumin	115	27-05-18
Mango	90	29-05-18
Apple	129	31-05-18
Orange	19	02-06-18
Banana	70	04-06-18
Guava	107	06-06-18
Cherry	120	08-06-18
Sweet	22	10-06-18
Papiya	128	12-06-18
Litchi	70	14-06-18
Jack Fruit	20	16-06-18
Lemon	35	18-06-18
Ginger	131	20-06-18
Garlic	32	22-06-18
Cumin	27	24-06-18
Mango	43	26-06-18
Apple	14	28-06-18
Orange	108	30-06-18
Banana	76	02-07-18
Guava	63	04-07-18
Cherry	104	06-07-18
Sweet	49	08-07-18
Papiya	88	10-07-18
Litchi	29	12-07-18
Jack Fruit	76	14-07-18
Lemon	131	16-07-18
Ginger	135	18-07-18
Garlic	98	20-07-18
Cumin	63	22-07-18

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Regards
Loknath

 

[ranman256]

1. I think you are using the wrong tool....you want ms Access.
2. Have you tried using pivot tables in excel?

 

[My Aswer Is This]

Had to look up definition of

lakhs​

.
Says

lakhs​

means hundred thousand.

So if you have 4-5

lakhs​

rows of data on 4 sheets and you want to do evaluation on all this data.
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Peter_SSs]

Hint: Best to keep sample data smaller than what you provided.

Assuming ..
- The data like you have shown is in columns A:C of each sheet with headings in row 1
- The dates are real dates (numbers) not text
- You can set up the 5th sheet, called "Summary" like below, entering your criteria in range A2:C2. (The name & layout could be different but the code would need to be adjusted to match)

Book1
	A	B	C	D
1	Product	Start Date	End Date	Volume
2	Mango	1-10-2017	31-03-2018
Summary

Then try running this macro

Sub TotalSales()
  Dim ws As Worksheet
  Dim sProd As String
  Dim dStart As Date, dEnd As Date
  Dim a As Variant
  Dim i As Long
  Dim TotSales As Double
  
  With Sheets("Summary")
    sProd = LCase(.Range("A2").Value)
    dStart = .Range("B2").Value
    dEnd = .Range("C2").Value
  End With
  For Each ws In Worksheets
    If ws.Name <> "Summary" Then
    a = ws.Range("A1", ws.Range("C" & ws.Rows.Count).End(xlUp)).Value
    For i = 2 To UBound(a)
      If LCase(a(i, 1)) = sProd Then
        If a(i, 3) >= dStart Then
          If a(i, 3) <= dEnd Then TotSales = TotSales + a(i, 2)
        End If
      End If
    Next i
    End If
  Next ws
  Sheets("Summary").Range("D2").Value = TotSales
End Sub