Compiling records

[bthomp]

I have several worksheets (monthly) that have rows of accounting data on. there is one column that i use to say if a particular invoice has been paid or not.

What I am aiming to achieve is the ability to compile all non-paid (ie rows with nothing in that column) onto a new worksheet, to make up a master list of unpaid invoices.

I have done some VBA before but only in word.

I would appreciate any help with this,

Thanks,

Ben,

 

[MacroManII]

Hi,

You could autofilter and select all the blank cells in this particular column. Then cut and paste to a new sheet.

MacroManII

 

[bthomp]

I have a filter in place, but I was hoping to do it automatically, from several workbooks at the same time.

 

[MacroManII]

You can loop through all the open workbooks
and apply your filter and copy the rows to one
new file ... something like this

Sub FindBlanks()

Dim Wb As Workbook
Dim sNewBook As String
sNewBook = "Exceptions.XLS" 'This is book to paste to


For Each Wb In Workbooks

If Wb.Name <> sNewBook Then
'Add your filter copy and paste code here
End If
Next Wb

End Sub

 

[bthomp]

cheers MacroMan

ill give that a go and report back.


-ben

 

[SkipVought]

Hey Ben,

You're making a BAD mistake that most spreadsueet users make -- chopping similar data up into separate sheets. You are looking at your spreadsheet application from a REPORT perspective rather than a DATABASE perspective.

Lets assume that you had ALL you invoice data in ONE SHEET that included a column for Date. It would simply be a matter of reporting to filter the date for a particular month and in this case, filtering the paid/not paid column to display only the unpaid invoices.

Data maintenance and data analysis is MUCH better performed on a single data source than on multiple sources.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bthomp]

Your right Skip, but the compiled data i end up with is sent by email. they are only interested in the subset of non-invoiced data. the main sheet i use has lots of extra calcs for cost tracking that only i need to see.

as the compiled data could be made up of non-invoiced lines over many months i need to automate it.

also, the complied data sent would get overwritten each month with an updated list and sent off by email again, i would never be editing the complied data.

i think that makes sense lol

 

[SkipVought]

Then I'd just append ALL the sheets into one each month and contend with mining the data of interest using filter/subtotal/pivot table functionality.

set wsThis = MyConsolidationSheet
for each ws in WhateverWorkbook.Worksheets
  ws.usedrange.copy _
    destination:=wsThis.cells(wsThis.usedrange.rows.count+.row, 1)
next

This will copy the heading for each sheet too. If this is a hassle then...

set wsThis = MyConsolidationSheet
for each ws in WhateverWorkbook.Worksheets
  with ws.usedrange
    range(cells(.row+1, .column), cells(.rows.count+.row-1, .columns.count+.column-1)).copy _
      destination:=wsThis.cells(wsThis.usedrange.rows.count+.row, 1)
  end with
next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com