Populate Sheet from Database

Thanks PCI

The blue table in my workbook is data coming from an accounting system. the Sheet A and Sheet B blelo the Blue Tableis what I want to end up with.

What I am trying to do is produce a Sheet (eg Sheet A and Sheet B) where the Manger can manually key in into the cells in the Forecast and Columns ... Once this manual entry has been made then I can use Pivot Tables, PowerBI etc.

Thanks again

Allister

I'm not sure about the figures in B.
This is how I read your problem.

Please Login or Register  to view this content.

Hi Jindon

Thank You for your effort in providing me with a potential solution.

How would the following be best treated

1. tour solution does a great job at setting up the initial structure for each Branch. My comment relates to what happens after the initial creation of a Sheet. Can VBA Test if there is a Sheet for the Branch? - if there is a sheet for the branch then test if the Account exists - if the account does not exist then insert a row in the correct sequence for the account. If the Account does exist then do nothing. If there is not a Sheet for the Branch then create one as per your code.

2. If Table 1 in my example is a Power Query Output Table - What I want to achieve is for the Sheets A and B to be dynamic - so as values in the Table changes so do the values in Column G of sheets A and B.

Thank You

1)
- Does "Account" mean "Name"?
Let's take A sheet
it has

Stock Code	Item	Name	Value
1300	Pants	Clothing	100
1300	Pants	Clothing	120
1300	Pants	Clothing	300

When the main data is refreshed, how do you determine the one to update the "Value"?

Suppose, 1300 is now have 1 row after the refresh of the power query in Sheet1.
How should it be done?

That happens everywhere in this forum...

So, you should work harder than ever to find such threads from now.

Originally Posted by jindon

Suppose, 1300 is now have 1 row after the refresh of the power query in Sheet1.
How should it be done?

On sheetA there will be one row for 1300 with the total of all rows in table 1 for Branch A stock code 1300.

I trust this answers your question.

Allister

Originally Posted by AliGW

@AllisterB - please don't ignore contributors to your thread - acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

Do I need to mark a reply as a solution if it doesnt work or doesn't give me a solution I can use or is not the best solution.?

If they are unique, no problem, but your sample had duplicate rows with different values in each worksheet as the result and so I coded as such.
If your uploaded sample workbook is not correct, I need to see the correct result.
Otherwise I can not write a code without the clear logic.

Originally Posted by jindon

If they are unique, no problem, but your sample had duplicate rows with different values in each worksheet as the result and so I coded as such.
If your uploaded sample workbook is not correct, I need to see the correct result.
Otherwise I can not write a code without the clear logic.

Tomorrow I will send better sample to clarify.

Thank You

Originally Posted by Bo_Ry

Please try Power Query

branch A can be changed

Please Login or Register  to view this content.

column Forecast, Difference, Comment are manually added.

Thank you... I think I may have have an alternate approach for solution do all hood

Originally Posted by AllisterB

Tomorrow I will send better sample to clarify.

Thank You

I have attached an updated file that I trust makes things clearer. Table 1 now only includes one instance for each Stock Code. This post replaces Post #6.

From your earlier solution it appears that VBA can produce an initial output that a manager can then use to manually input for each row values and/or formula into the Forecast and Comment cells.

When Table 1 changes, I want to be able to update the output while still retaining any contents of the Forecast and Comments cells. For clarity: if there is anything in the Forecast and/or Comment cells on a row then that needs to be retained intact after the update has occurred. If the contents of the Forecast and/or Comment cells was a formula, then this formula needs to work as it did before.

Also please note that It is okay for a stock code that no longer exists in Table 1 to now not show in the updated output, so long as there was previously no contents in the Forecast and/or Comment fields of the output.

My Question is how can this be done?

Thank You

Allister

Didn't test the code hard, so may have bugs...

Please Login or Register  to view this content.

Hi Jindon

Thank you for your efforts in modifying the macro.
When I ran it I got an automation error on the row Set dic(a(i, 1)) = CreateObject("System.Collections.SortedList")
Kind Regards

Allister

Hi Jindon

Thank you

I have managed to get this to work - I'll test it tomorrow

Allister

Again thank you Jindon for your solution

I have noticed several things that I think are related to each other.

• When the macro runs it does update the output for any change in the Stock Code. However, if there is a change in the Branch then when the macro runs then that Stock Code is output to the new Branch Tab but it still
appears on the sheet for the Branch Value.

• If the Description of an Item (or for Name) Changes the output of the macro does not pick up the new description.

• If a record in Table 1 is deleted, then when the macro is run the record still appears in the output.

It seems to me that the macro is retaining data that is now not in Table 1.

Is there a fix for this?

Leave "test" procedure as it is and replace other 2 private sub procedures with the folloiwng.

Please Login or Register  to view this content.

Hi Jindon

the macro runs the first time. But if I immediately run it again (no changes to Table1) I get a 1004 error No cells were found

The Debug highlights on row

r.CurrentRegion.Columns("g").SpecialCells(-4123).EntireRow.Delete in eth SubWS code.

Regards

Allsietr

Can not replicate such error, so no idea.

Hi Jindon

Could you run the attached - this will tell me if it is something with my system or not - thank you

Allister

This worked thanks.

I've done some testing and it works largely as I need it too - I'll press on with it now and see what I can do - thanks again for your efforts