Summarize Data from different Sheets via VBA

[BIGTONE559]

I'm in need of Help/Direction with trying to summarize data in multiple worksheets. The idea is to create a "summarypage" of the data on the 5 sheets. The position of the date remains constant. The Color/Descrip/Qty are all variables. On the summary page the items are grouped by date and displayed beginning in cell "A6". . .

Please Help

 

[Akinrotimi]

Hi,

For the macro to work please follow the folowing steps:

1. The sheets(1-4) must follow the format you setup in sheet2
2. Leave sheet "summary" completely empty
3. Go to macro-record macro-stop macro
4. Paste the code on the newly created macro.

Cheers!

Rotimi

 

[hiker95]

Akinrotimi,

You are missing the beginning of your code:
Sub Name_Of_Macro()

It would also help when you post code, that you wrap the code in code tags.

If posting VBA code, please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this



BIGTONE559,

Can we have a screenshot of what the Summary sheet should look like after a macro?

 

[hiker95]

BIGTONE559,

The idea is to create a "summarypage" of the data on the 5 sheets.

You have supplied screenshots of only 4 of the 5 worksheets. Can we have a screenshot of Sheet5?

On the summary page the items are grouped by date and displayed beginning in cell "A6". . .

On the screenshot of worksheet Summary, your raw data should begin in cell A8?

 

[hiker95]

BIGTONE559,


Sample worksheets:

Excel Workbook
	A	B	C	D
1	Summary Page
2
3
4
5
6
7	Dates	Color	Description	QTY
8
9
10
11
12
13
14
15
16
17
18
19
20
Summary

Excel Workbook
	A	B	C	D
7
8			Date	7/3/2011
9	Color	Descrip	Qty
10	Green	Pie	12
11	Yellow	Cake	13
12	Red	Ice Cream	174
13	Brown	Twinkie	19
14
Sheet2

Excel Workbook
	A	B	C	D
7
8				7/1/2011
9
10	Green	Lollipop	12
11	Yellow	Snickers	13
12	Red	Cake	174
13	Brown	Snickers	19
14
Sheet3

Excel Workbook
	A	B	C	D
7
8				7/2/2011
9
10	Green	Lollipop	15
11	Yellow	Snickers	21
12	Red	Cake	485
13	Brown	Snickers	12
14
Sheet4

After the macro in worksheet Summary:

Excel Workbook
	A	B	C	D
1	Summary Page
2
3
4
5
6
7	Dates	Color	Description	QTY
8	7/3/2011	Green	Pie	12
9	7/3/2011	Yellow	Cake	13
10	7/3/2011	Red	Ice Cream	174
11	7/3/2011	Brown	Twinkie	19
12	7/1/2011	Green	Lollipop	12
13	7/1/2011	Yellow	Snickers	13
14	7/1/2011	Red	Cake	174
15	7/1/2011	Brown	Snickers	19
16	7/2/2011	Green	Lollipop	15
17	7/2/2011	Yellow	Snickers	21
18	7/2/2011	Red	Cake	485
19	7/2/2011	Brown	Snickers	12
20
Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SummarizeData()
' hiker95, 08/10/2011
' http://www.mrexcel.com/forum/showthread.php?t=570236
Dim wSum As Worksheet, ws As Worksheet
Dim LR As Long, NR As Long
Application.ScreenUpdating = False
Set wSum = Worksheets("Summary")
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    With ws
      LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
      NR = wSum.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      wSum.Range("A" & NR).Resize(LR - 9).Value = ws.Range("D8").Value
      wSum.Range("B" & NR).Resize(LR - 9, 3).Value = ws.Range("A10:C" & LR).Value
    End With
  End If
Next ws
wSum.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the SummarizeData macro.

 

[hiker95]

BIGTONE559,

If you need the data in worksheet Summary sorted, then let me know how you want the data sorted.

 

[Akinrotimi]

Hiker,

Thanks for putting me through on d coding link here.Please kindly test my code and confirm whether it is working.
I told him to insert the code appropriately thereby makind the code name irrelevelant.I however accept all your explanations on this.Please test my code and confirmed whether it did work.

Thanks

Rotimi

 

[hiker95]

BIGTONE559,


Sorry, I missed your worksheet Sheet1 screenshot.

Be right back.

 

[hiker95]

BIGTONE559,


BIGTONE559,


Sorry, I missed your worksheet Sheet1 screenshot.


Sample worksheets:

Excel Workbook
	A	B	C	D
1	Summary Page
2
3
4
5
6
7	Dates	Color	Description	QTY
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Summary

Excel Workbook
	A	B	C	D
7
8				7/2/2011
9
10	Green	Lollipop	Big	15
11	Yellow	Snickers	Small	21
12	Red	Cake	Medium	485
13	Brown	Snickers	Big	12
14
Sheet1

Excel Workbook
	A	B	C	D
7
8			Date	7/3/2011
9	Color	Descrip	Qty
10	Green	Pie	12
11	Yellow	Cake	13
12	Red	Ice Cream	174
13	Brown	Twinkie	19
14
Sheet2

Excel Workbook
	A	B	C	D
7
8				7/1/2011
9
10	Green	Lollipop	12
11	Yellow	Snickers	13
12	Red	Cake	174
13	Brown	Snickers	19
14
Sheet3

Excel Workbook
	A	B	C	D
7
8				7/2/2011
9
10	Green	Lollipop	15
11	Yellow	Snickers	21
12	Red	Cake	485
13	Brown	Snickers	12
14
Sheet4

After the macro:

Excel Workbook
	A	B	C	D
1	Summary Page
2
3
4
5
6
7	Dates	Color	Description	QTY
8	7/2/2011	Green	Lollipop	15
9	7/2/2011	Yellow	Snickers	21
10	7/2/2011	Red	Cake	485
11	7/2/2011	Brown	Snickers	12
12	7/3/2011	Green	Pie	12
13	7/3/2011	Yellow	Cake	13
14	7/3/2011	Red	Ice Cream	174
15	7/3/2011	Brown	Twinkie	19
16	7/1/2011	Green	Lollipop	12
17	7/1/2011	Yellow	Snickers	13
18	7/1/2011	Red	Cake	174
19	7/1/2011	Brown	Snickers	19
20	7/2/2011	Green	Lollipop	15
21	7/2/2011	Yellow	Snickers	21
22	7/2/2011	Red	Cake	485
23	7/2/2011	Brown	Snickers	12
24
Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SummarizeDataV2()
' hiker95, 08/11/2011
' http://www.mrexcel.com/forum/showthread.php?t=570236
Dim wSum As Worksheet, ws As Worksheet
Dim LR As Long, NR As Long
Application.ScreenUpdating = False
Set wSum = Worksheets("Summary")
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    If ws.Name = "Sheet1" Then
      With ws
        LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
        NR = wSum.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        wSum.Range("A" & NR).Resize(LR - 9).Value = ws.Range("D8").Value
        wSum.Range("B" & NR).Resize(LR - 9, 2).Value = ws.Range("A10:B" & LR).Value
        wSum.Range("D" & NR).Resize(LR - 9).Value = ws.Range("D10:D" & LR).Value
      End With
    Else
      With ws
        LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
        NR = wSum.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        wSum.Range("A" & NR).Resize(LR - 9).Value = ws.Range("D8").Value
        wSum.Range("B" & NR).Resize(LR - 9, 3).Value = ws.Range("A10:C" & LR).Value
      End With
    End If
  End If
Next ws
LR = wSum.Cells(Rows.Count, 1).End(xlUp).Row
wSum.Range("A8:A" & LR).NumberFormat = "m/d/yyyy"
wSum.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the SummarizeDataV2 macro.

 

[hiker95]

Akinrotimi,

Thanks for putting me through on d coding link here.Please kindly test my code and confirm whether it is working.

If you post all of your macro code (including the name of the macro), using code tags , I would be happy to test it for you.