I hope someone here can help me with the following (it should actually be rather simple).

I have an excel worksheet containing Products, some details to each product and a Page number that indicates which page in the catalouge a certain Porduct is on.

What I need is to link those products to certain pages in my indesign catalouge.
However there is sometimes changes to the products, and some even disappear.

I need different worksheets for each page in my excel map.
Those sheets have to autoupdate if something on my first "input" sheet is changed.

For each pagenumber that exists in my "pagenumber"-Colum on my input worksheet a different worksheet needs to be created.
In those the content must always be like this:
A Header Row (based on my input sheet) and then in each row the specific products with their properties


What I have come up with so far is not very useful, but a start:



My Input Worksheet basically looks like this:

Product Number; Product Name; Product Description 1; 2 and; 3; Price; catalouge page; some more colums that are not necessary
P133; Box Large; A Large Box 12x12; ""; ""; 4$; P. 1.5; asdf;asdf


Thank you!

I think I get the logic of your problem.

The macro basically needs to do the following:

1) Open the input worksheet
2) Iterate the page number column on input worksheet
3) For each page number cell on input worksheet, check if there is another worksheet created with that page number. If not created, create it. Immediately after creating, copy header row of input worksheet (e.g. that contains titles Product Number; Product Name; Product Description 1; 2 and; 3; Price; catalouge page;)
4) For each page worksheet created, copy all data from input worksheet corresponding to that page

With regard to steps 3 and 4:
3) I think you know how to create a worksheet
- With regard to copying header, I would select range A1 to whatever and do copy and then do paste special values in the same range on the destination worksheet (e.g. page worksheet) - if you need help with the function, let me know and I will look it up for you (I've used it before). This copy paste header should only be executed once at page worksheet creation time.

4) Same principle applies to copy data to page worksheets, select the row of data that you are currently iterating, copy and do paste special values on the next blank row beneath header on the page sheet

- Also with regard to 4, given that you mentioned that the data could change, that should not matter. Every time you run the macro it will iterate through all data on the input worksheet and copy it to the page worksheets. And in case data gets removed from input worksheet or re-ordered, I would recommend that as part of your macro before you do any copying that you do a clear cell contents on the data worksheets (below header row) - this will erase all data previously there excluding the header row.

On the new page worksheets you are creating, you might want to also auto set column width (e.g. you know how when you click in between columns it adjusts the column width to match contents). I think if you use record macro you should be able to find out what function enables you to do this automatically for each column with data.

------

Another tip regarding the functions to do all this. I would recommend that you use the record macro function in excel. Basically record yourself going through the whole process manually. Then stop the recording and check the recorded macro - it should give you all the code you need to automate the process (ofc you'll need to add your own condition statements but that should be easy enough.

This post was edited by PartyInMyPants on Jan 3 2014 04:43am

Yes exactly that is what i need help on. I am to stupid to do if then
I dont know how to copy data and make it so it always writes in a new row for each product

in reagarding the changing data: yes thats why i need a macro and cannot do it once manually!
I do not need to set the colum with, because im exporting the data in the sheets to indesign which then handles the layout within itsself.

Help would very much be appreciated

anyone? This cant be too complicated for you pros!

This post was edited by Hooo on Jan 6 2014 02:18am

if you want a more detailed solution (with pseudo-code and whatnot), post some screenies of that spreadsheet so we have a better idea of exactly what you're going for