Excel VBA AUTOFILTER & Copying table into Word from Excel Range 1

[jwoo6452]

I am pasting a range into a word document from an excel range after running autofilter (from VBA) on the range.

Worksheets("Sheet1").Range("YourExcelrange").AutoFilter Field:=2, Criteria1:=chosenValue
YourExcelrange.Copy
YourWordDocument.Bookmarks("YourBokmark").Range.PasteSpecial

This works fine on one computer with Word 10.0 Object Library, and creates a 37 KB word document; however, on another computer with Word 9.0 Object Library, it creates a 1.5 MB file, and takes too long; perhaps it is copying all the data into the word document somewhere, not just the visible rows? Other ranges pasted this way (without using autofilter) are small in size. Any suggestions that will eliminate this? ... DataType or Link? Or perhaps an alternate way from autofilter to extract a range of data from a table that is added to daily. (The selection is by TWO column values for a varying number of resulting rows.)

 

[tbl]

I would suggest that you use the VBA command to copy only visible cells instead of COPY which gives very variable results. It might be worth performing the COPY.VISIBLE within Excel to check if the result is indeed what you expected, and that no suppressed lines are being copied. I have had precisely the same problem using VBA code to perform filtering and ended up performing the copy/paste internally before exporting.

Richard

 

[jwoo6452]

I was hoping there was a simpler solution;
How did you account for a variable number of rows in the internally copied data so that the table in Word includes all the rows needed (and no more)? I would assume countif or select and offset until blank...

 

[jwoo6452]

... Figured it out. After copying the entire filtered range to the hidden sheet, I copied the still selected range that was pasted without the rows hidden by autofilter. The copied selection was then pasted into Word. Another solution is to PasteSpecial as a Metafile type. This will not copy any invisible data and eliminates the need for the hidden sheet. Thanks for the help tbl!