Macro for extract data into new sheet

Hi,

I need a macro which is extract data from attached sheet into new sheet with below format -

Column A - Account Name (From the column A in attached sheet)
Column B - Project Name (From the column B in attached sheet)
Column C - Put the name of resources ( From the column C in attached sheet). Need only resources who charged billable hours in column "G" in attached sheet.
Column D - Include Billable hours. ( From the column G in attached sheet)

I have included only few projects in attached sheet but I have lot of project so the macro will extract the data till end.

Thanks in advance

You have way too many merged cells to work with. May be a good idea to set up the data sheet in a workable layout, then have another sheet to extract the information as you need it.

This is really easy with Power Query:

Please Login or Register  to view this content.

Change the filepath / name as required, to point at your report file.

See attached workbook for a worked example.

Click 'Power Query > Get External Data > From Other Sources > Blank Query'

The Query Editor window will open. Click 'Advanced Editor'.

Paste in the query code as above - replace everything that's already in the query. Amend filepath / filename as required. Click 'Done'

You'll see the Applied Steps of the Query in the Query Settings pane, and the query output in the editor window.

Click 'Close & Load' - the query output will be put in a table, on a new worksheet. You can simply refresh this query to update the output, when your source file changes.

Great, happy to help. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also 'Add Reputation' to those who helped you. Thanks.