VBA to manipulate pivot table in to 'normal' table for account software upload

Hi experts,

I need help to manipulate pivot table data into a specific template so I can upload this template into accounting software.

I have attached a file that shows an example of the pivot table data, the Acct template which will be uploaded to the accounting software, and a description tab for the Acct template tab to explain the structure and content of the template.

I need help with the following as I am a VBA beginner:

1. For each row in the pivot table, I need the Acct Template tab to be split into two rows, one row showing the income and the VAT and the other row showing the service charge.

2. The split row with the income and VAT needs to show 'is' for the Class column and 'SALES' for the description column

3. Each store has a letter(s) code, the Marks column needs to show the store code as below. The name of the stores in part of the text in column A in the pivot table.

Greengates = r
Shipley = l
Crossgates = xg
Starbeck = st
Dewsbury =db
Hunslet =hl

4. The VAT values need to have the lower case letter 'l' after the numerical figure

5. The Reference, Rec. and Rec. Balance columns need to stay blank.

I realise this is a lot of work but if someone could help me with points 1 and 2 I am sure I can work out the rest.

Many thanks!

Hi Erwin,

Thanks for this, it is almost there. I found a couple of issues:

1. I get two rows for the data from the 9th which is great but, I only get one row (the SALES row) for the 10th, the sales commission row is missing
2. When the data is added to the table, it is offset by one column to the right. So the data that should be in column 1 is in column 2

Can you help fix these issues?

Thanks,
Sahil

Hi Erwin,

I have attached my full spreadsheet in case some of the extra data I have in there is making a difference. If you run the Sub New_List() macro, at the bottom of the long list of code, you will see the data populate the 'MM Template' tab with the following issues:

1. The last row of data in the pivot table is only showing the SALES row after running the macro
2. The data when added to the table is offset by one column to the right
3. The vlookup for the store code seems to break partway down the list and all of them below row 15 shows 'xg'

Thanks,
Sahil

Hi Nankw83,

This works perfectly apart from the date. It shows is at MM-DD-YYYY and I need it as DD-MM-YYYY. All the data is for August so it should be DD-08-2021.

I have tried using the below code to change it but it doesn't seem to do anything, presumably because it thinks it is the in the DD-MM-YYYY format to begin with.

Sheet1.Range("A2", "A50000").NumberFormat = "dd-mm-yyyy"

Thanks,
Sahil

Amazing, this has worked now!