Macro Based Spreadsheet with XML information

[FOM]

Hi there
I work for a Hotel and we use Opera Cloud.
I extract a report from this and it unfortunately is downloaded in XML
I then have to adjust the sheet and then copy it into another sheet to Upload to a Housekeeping report on Opera Cloud
Its time consuming and the receptionist dont get it right

So I want to make a Spreadsheet that I can have them run Macro's on and it does what we require for them.

Example. Download the XML information into Sheet 2 - Make a macro that adjusts the sheet to the Format I need and then arrange the Categories the way it needs to be.
Then a Macro copies this information from sheet 2 to Sheet 1.

Please see attached

So image 2.. with red writing is how the sheet needs to look after u extract information from image 1
There is a HUGE amount of Columns I need to delete then arrange the order in how they are placed then change the date format before i have the macro copy it to the final sheet (image 2)

Can anyone give me an idea... I have made macros before but this is constantly coming up with debug errors..

Basically want a job that takes 1 hour to be 5 minutes in the end

 

[jkpieterse]

You could have that all work in one large step if you use Data, Get Data, From File, From XML to get your data in and subsequently mangle the data: re-order columns, rename them, get rid of the ones you don't need, etcetera.

 

[FOM]

I have no idea what u mean

 

[jkpieterse]

Just open that menu and give it a try!

 

[FOM]

I have tried the option you suggested above and it does not work - Can anyone else assist me please.
Ideally i want it to be macro based for my staff to use and not have human error

 

[jkpieterse]

Not sure what you mean by "it does not work". Have a look at this page for some pointers:

XML Data manipulation with a Power Query

documentation.n-able.com