Consolidate mixed number and text data from multiple sheets

evanmarch

New Member
Joined
Sep 18, 2006
Messages
2
I have a workbook with a sheet for each month. On each monthly sheet I have created formulas that single out every line item for which I have not received payment. Each of these lines include dolar amounts, sales order number and description, all in seperate colums. I would like to create a consolidated sheet showing only these unpaid items but all in one place. Can this be done?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi evanmarch
Welcome to the board

2 solutions
Manual - in a month sheet use autofilter on the column that has the formulas that single out the unpaid items. Display only the unpaid items and copy them to the summary sheet

Automatic - do the same thing in vba cycling through the 12 sheets. To get started record a macro doing it manually for one month.

Hope this helps
PGC
 

evanmarch

New Member
Joined
Sep 18, 2006
Messages
2
When I set up the macro, how do I handle the issue of varying numbers of items. Sometimes I may need to cut and paste 10 items and sometimes 50. Can a macro recognize the different lengths or would it just cut and paste theh same number of items over and over?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi evanmarch

I prepared a small example. This is just to get you started. You'll have to adjust it to your case.

I tested it and I'll post the test data, part in this post and part in the next.

This is the example configuration

All worksheets have the same structure:
- the first row is a header row
- data is in columns A:D
- the values in column D (Status) single out the unpaid items
- for each item, column D is never blank, either has "Unpaid" or something else

The data is in the 3 first worksheets.
The result is in the worksheet "Summary"
The code always starts with a clean "Summary" sheet (clears contents).

I hope this helps you to build your solution
PGC

Code:
Option Explicit

Sub CopyUnpaid()
Dim wksSumm As Worksheet, wks As Worksheet, rCopyFrom As Range, i As Integer
    
Set wksSumm = Worksheets("Summary")
wksSumm.UsedRange.ClearContents
wksSumm.Range("A1:D1").Value = Worksheets(1).Range("A1:D1").Value
For i = 1 To 3
    Set wks = Worksheets(i)
    wks.Columns("D").AutoFilter Field:=1, Criteria1:="Unpaid"
    Set rCopyFrom = wks.Range("A2:D" & wks.Range("D" & wks.Rows.Count).End(xlUp).Row)
    If rCopyFrom.Row<> 1 Then _
        rCopyFrom.SpecialCells(xlCellTypeVisible).Copy Destination:= _
            wksSumm.Range("A" & wksSumm.Range("D" & Rows.Count).End(xlUp).Row).Offset(1)
    wks.AutoFilterMode = False
Next
End Sub
Book1
ABCDE
1CodeItemPriceStatus
2ID001Chair4.5Unpaid
3ID002Table16.5Paid
4ID003Book3.1Paid
5ID004PC134Unpaid
6ID005CD23Unpaid
7ID006scissor11Paid
8ID007paper8Unpaid
9ID008Glue23Paid
10ID009Clips2Unpaid
11
12
Sheet1
Book1
ABCDE
1CodeItemPriceStatus
2ID0010Pencil4.5Paid
3ID0011Rubber2Paid
4ID0012Pencil3.1Paid
5
6
Sheet2
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
And here goes the last data worksheet and the Summary worksheet that was built automatically by running the code.
Book1
ABCDE
1CodeItemPriceStatus
2ID0020Car50Unpaid
3ID0021Bike20Paid
4ID0022Boat40Unpaid
5
6
Sheet3
Book1
ABCDE
1CodeItemPriceStatus
2ID001Chair4.5Unpaid
3ID004PC134Unpaid
4ID005CD23Unpaid
5ID007paper8Unpaid
6ID009Clips2Unpaid
7ID0020Car50Unpaid
8ID0022Boat40Unpaid
9
10
Summary
 

Forum statistics

Threads
1,136,517
Messages
5,676,314
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top