Copy Paste each cell to a new page

[Singh_Edm]

Hi
I have a 1Col x 1430Row table in excel. I want to copy and paste every cell as a new page in a Word document (Landscape orientation) while retaining the color formatting.
If importing from excel is an issue, I can copy paste the table in word and then use your suggested code from there.
Kindly assist.

[eNGiNe]

One possible approach would be to copy/paste the spreadsheet content into Word as plain text (paste special) and then apply a style with 'start on new page' (format > paragraph > line and page breaks > page break before; include the required colour formatting in the style, too.

For more flexibility, though, I'd start by adding two things to the spreadsheet:
a column before the data, and an empty row between each row with content. Why? then you can add tags (for example, the colour name before a cell with coloured content and an arbitrary [empty] tag before each empty cell.

Paste special these two columns into Word, set up any coloured content styles you need (with the "break before" property) plus a plain-text style with no break before, then use search/replace on the tags to apply the styles as required.

There may, of course, be other approaches!

[Singh_Edm]

I will try these now and let you know.

[Singh_Edm]

A sample content of a cell of my table in excel is below.

Be kind; because everyone you meet, is facing some kind of a battle

I used a macro to remove the punctuation and color words so now this cell looks like this.

Be kind because everyone you meet is facing some kind of a battle

I believe I wasn't clear enough in my post in explaining my color scheme. So I have my table in excel in both formats: 1. with punctuation 2. with color scheme and no punctuation.

If importing from the 'table with color scheme' is hard, if there is an approach that would would import each cell of from the 'table with punctuation' I can try and find a macro in word to get my color scheme.

Would the approaches you suggest work now? Please suggest.

And, if you can't think of a way,
Can you please reword my original post + this post combined so my question is clearer in a single post?

[eNGiNe]

Revised now I understand the colouring is at character level, not cell level …

• content: single-column Excel, some words in each cell coloured – Be kind, because everyone you meet is facing some kind of a battle
• goal: multi-page Word, each cell on separate page, colour preserved

What I did …

1. selected the single column in Excel
2. pasted it into Word – this gives a table
3. converted the table into text (Table tools, Layout, Convert to text)
4. selected all the text, applied a heading style with Page break before and text colour Automatic

What you could do to finish the conversion is …

set up character styles and use those to apply your special colouring: this will make it easier to find/retrieve coloured words, and to add new records by hand if you need to

In general, style-driven changes are preferable to local overrides <g>

HTH!

[Singh_Edm]

Regarding bullet point 4, I don't understand "applie a heading style 'WITH PAGE BREAK BEFORE'? So as you are rewording my question you are also suggesting I try this, right?

I'm not sure how to apply a 'page break before'
Many thanks for taking out the time for that.

[eNGiNe]

• Check recent postings for pointers to information about styles – you really need at least a basic understanding of this mechanism if you want to be productive with Word.
• A style defines a lot of properties for a paragraph or a block of text within a paragraph. By defining a style once, you can make sure all the paragraphs (or text) you apply the style to have the same properties; consequently, you can then modify the style and change the properties of all the content that style applies to. 'Page break before' is one of the properties you can specify with a paragraph style, and the easiest way to get the result – one piece of text per page – you want.

[Singh_Edm]

Ok so I was able to put page breaks. I thought the wildcard idea would work but it didn't. So now I have a word file, without colors but with punctuation marks ; , .

I want to differently color each word that ends with the above symbols. Can't get this portion done.

[eNGiNe]

You'll have to hope that someone else can help you with Word's implementation of wildcards :-} It doesn't seem to include the concept "word", or at any rate I can't persuade it to find/select {word};

btw, when you're at the stage with colours (imported from the original Excel file) but no special characters – have you tried searching for colour and applying a style?

I'll watch and learn on this part of the question.

[Singh_Edm]

If I import the colored excel file (no special characters) and paste that as plain text, as per your question, can I still search for colors to apply a style? Because once I paste as text to apply 'page break before' everything is pasted just as black text.

[Charles Kenyon]

When you paste as plain text, the color formatting will be lost.

[Singh_Edm]

http://www.vbaexpress.com/forum/show...ecial-characer

The second problem is Solved there by someone.

[eNGiNe]

Originally Posted by eNGiNe

What I did …

1. selected the single column in Excel
2. pasted it into Word – this gives a table
3. converted the table into text (Table tools, Layout, Convert to text)
4. selected all the text, applied a heading style with Page break before and text colour Automatic

This option does not use paste special, so the Excel colours are still there and you can use them to drive search/replace.