Summarize Multiple Worksheets Based on Criteria

**xybadog** · 05-30-2014, 03:26 AM

Can someone suggest some code for me please as I have virtually no knowledge of VBA and I've been advised that a macro would be the best solution for my problem.

I want a summary list of data from all columns of worksheets that contain the target data and only where the value in col D is 1. So, in the attached example, the data to be checked is on Sheet1 and Sheet2 and the result should be shown on Summary sheet. There will be some tabs that don't need to be checked for the data so it's not a case of check all other tabs except for Summary.

**AB33** · 05-30-2014, 04:19 AM

Try the attached.

**xybadog** · 05-30-2014, 06:46 AM

Originally Posted by AB33

Try the attached.

Thanks for this. I've copied and pasted your code into my actual workbook after making a couple of changes where I thought I should make them but maybe I'm wrong. In the example, the criteria to check is in col D but in my actual workbook it's in col H.

In your script what do I need to change to the reference column from D to H? Also, each reference worksheet will range from row 2 to row 101.:

Sub ConsolidateData()
Dim ws As Worksheet, wsMaster As Worksheet, LR As Long
Application.ScreenUpdating = 0
Set wsMaster = Sheets("Summary")
wsMaster.UsedRange.Offset(1).EntireRow.Clear
For Each ws In ThisWorkbook.Worksheets
With ws
If .Name <> wsMaster.Name And .Name <> "Total" Then
.AutoFilterMode = False
.Rows(1).AutoFilter 4, "1"
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
If LR > 1 Then
.Range("A2:D" & LR).Copy
wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
End If
.Rows(1).AutoFilter
End If
End With
Next ws

Application.ScreenUpdating = True
End Sub

**AB33** · 05-30-2014, 07:04 AM

Please Login or Register  to view this content.

Also the line above end if

Please Login or Register  to view this content.

**xybadog** · 05-30-2014, 08:39 AM

It almost works but for some reason is stopping at this line

Formula:

Please Login or Register  to view this content.

.

I've attached my actual workbook (with generic data) that I've applied the code to.

**xybadog** · 05-30-2014, 08:48 AM

I've added the macro to my proper workbook (instead of the example) and it keeps failing at

Please Login or Register  to view this content.

I've now attached my actual spreadsheet (with generic data) as I can't see where it's going wrong.

**AB33** · 05-30-2014, 08:51 AM

You want to filer the data by column H and yet, some of the sheets do not have column H.

**xybadog** · 05-30-2014, 10:04 AM

I've added some sheets to the exceptions and some sheets I've added a col H where necessary.

It works, almost, perfectly but, the result is showing the first row of data (row 2) of each tab even if it doesn't have "1" in col H.

**AB33** · 05-30-2014, 10:08 AM

Which sheet from the attached? If the sheet is not on attached, please attach a new one which shows the error.

**xybadog** · 05-30-2014, 10:17 AM

Sorry, now attached. If you look at, for example, record 200, 800, 1000, 1100 etc are showing on the summary even though their value in col H is 0.

**AB33** · 05-30-2014, 10:24 AM

Are you sure you are using the correct code? The old code was attached, but I had attached the amended code.

**xybadog** · 05-30-2014, 10:33 AM

It's still showing record 200 which it shouldn't but I can live with this.

Thanks very much for your help and your patience.

**AB33** · 05-30-2014, 10:35 AM

No, which sheet exactly is? Is it sheet 200-299?

**xybadog** · 05-30-2014, 10:37 AM

Yes, sheet 200-299 row 2

**AB33** · 05-30-2014, 10:55 AM

Sheet 200-299 has column H and there is single row which has a value of 1, so when the code loops through the sheets, it copies that single row in to summary. Or, am I missing something?

**xybadog** · 06-02-2014, 09:39 AM

Sorry took so long to get back but I've been unavailable.

You are right, there is only a single row (row 83) that has a value of 1 in col H but the first row of data (row 2) on tab 200-299 has a value of 0 in col H and this is showing in the results when it shouldn't. I've not edited the code from your last version and it seems to be showing this error for me. Any record that has "Destroyed See Previous Record" for example, in col D, these all have 0 in col H in their worksheet so don't meet the criteria to be included in the summary.

**AB33** · 06-02-2014, 10:30 AM

Okay! I can now see what is going. The filer data were correct, but the clear data commenced on the wrong row, hence the old wrong data was not deleted.

Change this line

Please Login or Register  to view this content.

**xybadog** · 06-02-2014, 11:28 AM

This works great. Thanks for all your help.

Summarize Multiple Worksheets Based on Criteria

LinkBack

Thread Tools

Rate This Thread

Display

Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Re: Summarize Multiple Worksheets Based on Criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] summarize information based on criteria using excel

[SOLVED] Summarize Based on Criteria

Summarize and Analyse data in multiple worksheets on one worksheet based on date range

Excel 2007 : Summarize data from multiple worksheets with multiple criteria

Summarize data based on multiple criteria

Bookmarks

Bookmarks

Posting Permissions