VBA solution to manual data entry task

[fabrecass]

Hi guys,

I have a workbook (SCDHD Report 2019) that needs updating daily from 2 other workbooks (titled OPENED and CLOSED), all 3 files stored on my desktop. I've been tinkering for days trying to get this to work but I can't do it with my limited knowledge and google. I've outlined the individual steps that need to occur below; can anyone help? I can attach the workbooks in necessary?


Open workbook "OPENED"
Filter workbook "OPENED" column C by "cancelled" and delete all rows
Unfilter column C on "OPENED" workbook
Paste count of records to next empty cell on row 23 of "weekly" worksheet in "SCDHD Report 2019" workbook
Filter out "Phone" from column P on "OPENED" workbook and paste count of records to next empty cell on row 27 of "weekly" worksheet on "SCDHD Report 2019"
Filter workbook "OPENED" column P by "Webchat" and paste count of records to next empty cell on row 31 of "Weekly" worksheet in "SCDHD REPORT 2019" workbook
Close workbook "OPENED"

Open workbook "CLOSED"
Count of records from "CLOSED" workbook pasted to next empty cell on row 24 of "Weekly" worksheet in "SCDHD REPORT 2019" workbook
Filter out "Phone" from column P on "CLOSED" workbook and paste count of records to next empty cell on row 28 of "weekly" worksheet on "SCDHD Report 2019"
Unfilter column P on "CLOSED" workbook
Filter column T on "CLOSED" workbook for dates within 2 days of date in column U and paste count of records to next empty cell on row 29 of "weekly" worksheet on "SCDHD Report 2019"
Close workbook "CLOSED"

[torachan]

Lot easier with the actual workbooks.
torachan.

[fabrecass]

This is frustrating, every time I click the paperclip to add the workbooks I just get a thin white bar appear and nothing else, tried in IE,edge chrome and firefox and get the same thing. Am I being a moron?

EDIT: i'm being a moron, workbooks attached to original post.

[fabrecass]

bump, no one?

[huuthang_bd]

Try this code on workbook 'SCDHD Report 2019'.
Please not that workbooks OPENED.xlsx and CLOSED.xlsx has to in the same location with workbook 'SCDHD Report 2019' and them has to be closed before running code.

Please Login or Register  to view this content.

[fabrecass]

Thank you so much for this, it's done a lot of what I wanted it to do there's just a few problems I've noticed

I've attached a worksheet with the correct figures vs the figures I get when I run the macro, also some (probably wrong) ideas as to why the counts are wrong

any ideas?

[huuthang_bd]

I don't see any records which has 'cancelled' on column C.
For clarification, with each information you want to get, please add a column and mark records match with the criteria. After that, show me that workbook.

[fabrecass]

Hi,

sorry for taking so long to get back to you. I've added instructions to the SCDHD Report 2019 workbook to state exactly what I'm looking for, hopefully this clarifies any miscommunications.

Hope you can help!

[fabrecass]

bump - anyone?