Macro to separate the data based on Column

Hi Friends

I have attached the spreadsheet example. The tab "Report" has system generated data which has data for four entities. I want to separate this data based on values in Column A. Sheet1 to Sheet4 is what i want to achieve using macro.

The macro should look at column A and where it has value 107001, it should copy the data into new worksheet. For any change in value in column A, it should copy the data into new worksheet and so on.

Is there anyway this can be achieved via macro?

Really appreciate your help.

Thanks in advance

Hi

A macro will achieve what you need however I did notice that the columns in the report tab do not add to respective subtotals
ie
Rental Income

10302 Retail Rent-Major 100
10304 Retail Rent-Speciality 100

Total Rental Income 100 This should be 200 unless my interpretation of the financial report is erroneous.

If you need the report to relect the revised mathematical equation ( and subsequently each entity tab that can also be rectified.

Please advise and I will try and solve your requirements.

Cheers

Hi

That's fine..I have changed the numbers hence it is incorrect. It should be 200. The system generated report doesn't have formula.

Thanks for your time and effort.

Hi

I have modified a number of cells in colum 1 of your report and highlighted the changed cells in red.

I also noticed Entity ID 407028 has the entity name Benowa Village and also Village Centre and assumed this was intentional.

However have managed to create a macro on a new sheet (Control) and all appears to work although somewhat slower than I anticipated. This has taken a simple approach and the macro will have to be modified if new entities are added or deleted.

To see the macro click developer\macro\Macr1 \edit.

If this answers your problem great if not please advise.

Cheers

Hi friends

I have managed to get macro. However, it repeats the heading from sheet3 onwards. Not sure what's the issue?

Can anyone please advise why it repeats the heading?

Thanks for your assistance.

Hi

Change row
Set Rng = Sh.Range("A3:A" & Sh.Range("A65536").End(xlUp).Row)

to

Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)

This seems to work

Cheers

Hi

I have done the changes as advised but it still repeats

Hi

My mistake I will continue to assist.

Cheers