Audit report

[mlk]

Hello,

I have a macro that looks at all excel workbooks in one folder and copies all rows (except row 1) for each of the worksheets that have a value in the first column and consolidates the data into one master worksheet. For example, if there are 12 workbooks, then all 12 will be automatically consolidated into one.

This is used to consolidate worksheets from multiple users into one spreadsheet.

In the event a user accidently loads a blank workbook in the folder, I would like a way to automatically identify which one it is. Currently this is manual - opening up the workbooks one by one to see which one had missing data.

If possible in the master file, I would like another tab to automatically log all the workbooks and worksheets that are in the folder as they are being reviwed by the macro plus identify the number of valid data rows (ie 0, 1, 4, etc)

Could someone help me with the coding on this as I'm still learning VBA.

Example of potential log:

Workbook, Worksheet, Data (# of rows)
Test File 1, Form, 1
Test File 2, Form, 2
Test File 3, Form, 0
Test File 4, Form, 1


I am attaching the code I have.

Thanks, Mary-Lou

[rylo]

Hi

Didn't test this at all, but see how it goes.

1) Make sure you work with a copy and keep your original intact.
2) Create a new sheet in your output file called Log.
3) Beside / above the additional code, I've put a comment 'new so you can see what I've done.

Please Login or Register  to view this content.

rylo

[mlk]

Hi, thank you very much the code changes.

I had to some minor adjustments (I bolded the changes below) as it crashed on "set logsheet". I added a line to add a "log" sheet plus I gave the original active sheet a name.

Your code works awesome and I'm wondering if it would it be possible to only count the actual data rows? Not the blank ones?

For example, in one workbook I had only 2 lines of data that started in row 3 and this appeared on the log as a 4 instead of 2. Similar thing for a workbook that had no data lines at all but the log showed a 1.

Thanks, Mary-Lou

Please Login or Register  to view this content.

[rylo]

Mary-Lou

What sort of data will be in the source files? Numeric, alpha numeric, numeric? Is this data constants, or is it generated by formulas? Are the blanks really blank, not spaces / zeros or the result of a formula ("")?


rylo

[mlk]

The data will be a combination of everything from columns A through to column CA.... but there should always be something in column A. If nothing is in column A, then this can be considered a blank row.

[rylo]

Hi

Again, didn't test this but try changing the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

rylo