Detect the presence of data in specific cell range and update table in another sheet

[ell_]

Hi, all

I have created a workbook which can generate PPT from Excel content. The contents of Excel need to be defined in Definitions sheet (in the table) accordingly, following the slide index in the template.pptx. My concern is, there is this one sheet that will update its content every time the data in cell K2 and M2 (DTE sheet) detects the similarities of data in DTE_Raw sheet (the raw data) - almost like filter function.

Now, is it possible to create a code in which it will automate the defining work of the table in DTE sheet? The details are as specified:

1. With every 5 data in A2:A20, the table will copy the header in A1 and "insert copied cells" to the row below the 5th data. So, assuming we have several sets of table (max is 3 tables with max of 5 records in each) in DTE sheet now, next step will be defining the table(s) automatically to the Definitions sheet (currently user has to fill in the details by himself but my superior does not want that). Here are the details need to be defined:

a. "Excel Page" = the sheet name ("DTE")
b. "Excel Object" = cell ranges starting from header to the row of A which only has record i.e. an example is shown in DTE sheet
c. "Excel Type" = "Range" for each new row
d. PP page = increment of 1, starting from PP slide 17
d. "PP Object" = "Table" for each new row
e. "Top" = 1.5, "Left" = 0.5, "Width" = 9.0

2. Determine the height of cells which have data (including header), i.e. A7:G12 or A13:G14 and the height will be updated to "Height" column in the Definitions sheet, according to their row - height will also have to be changed in terms of unit (divide by 72).

Your help will be really appreciated as I am currently in deep confusion, especially with number 2. That is why the description is quite lengthy as I am trying to be as specific as I can. But do ask if there's any question. Thank you in advance!

[ell_]

Anyone willing to give a hand? I can perhaps configure further if your code can only do number 2. Hereby I attached my previous code for number 2 but it is not fully as I wanted:

This code requires user to select the cell ranges first.

Please Login or Register  to view this content.

Would really appreciate a hand with this. I'm able to edit and understand code but still quite slow with creating complex code myself. Thanks in advance & have a nice day!

[ell_]

Hi, I'm still seeking for suggestion/answer. Anyone?

Thanks in advance!

[ell_]

Hi, again

I believe my post has been ignored as no one seemed to understand my issue. Hence, hereby I attached the template.pptx just in case you would like to try the PPT generator. As long as your Excel and template.pptx file are in the same folder, you can run the code.

Hope you will have better understanding of my explanation up there.

Have a nice day!

PPTX link: https://drive.google.com/open?id=1vu...jWfZ2Qfd2pL-Q1

[AliGW]

Probably no answers because you posted to the Excel Programming section - now moved to the Powerpoint Programming section.

[MrShorty]

Step 2 seems like it should be a simple .height property of the appropriate row/cell: https://msdn.microsoft.com/en-us/VBA...property-excel

[ell_]

Hi, MrShorty

Thank you for the reply. After days of trying, I finally managed to do the code but not fully. There's still a mistake. Here's my current issue if you're interested: https://www.excelforum.com/excel-pro...ml#post4827033