Macro to Copy from worksheet and paste to Word Document

I have a Worksheet that contains 1 record per row. The fields can range from a minimum of A thru K to a Maximum of A thru S. What I am trying to do is copy the fields to a template worksheet, one record at a time, then select the template and copy and paste it to Word. Each record would be a separate table in a Word document. My problem is how to check for data in columns K thru S, and if they are empty, not copy and not paste to Word so that I don't end up with a table with a bunch of empty cells. this is the Macro I am using:

Sub Greenbriar1()' You must pick Microsoft Word 8.0 from Tools>References' in the VB editor to execute Word commands.' See VB Help topic "Controlling One Microsoft Office Application from Another"' for more information.' Macro created by Marc F. Hennebery 11/25/2000

Dim appWD As Word.Application

' Create a new instance of Word & make it visible Set appWD = CreateObject("Word.Application.8") appWD.Visible = True

' Copy the data for the new document to the clipboard Sheets("Template").Select Range("A2:F14").Copy ' Tell Word to paste the contents of the clipboard into the new document appWD.Selection.Paste ' Close this new word document appWD.Selection.TypeText Text:=vbTab appWD.Selection.TypeParagraph Next i Sheets("Drug Regimen").Select appWD.ActiveDocument.SaveAs FileName:="I:\Medistat\Greenbriar Station 1 Summary " & Date$ appWD.ActiveDocument.Close

I have assumed that any data in columns K to S would be contiguous starting from column K.Post again if this is not the case.Celia

Posted by Marc Hennebery on December 08, 2000 1:27 AM

Re: Thanks for your help ....

K Thru S was contiguous, and it worked like a charm. Thank you for your help.

By the way, could this be modified to append the data from Worksheet "Drug Regimen" to another Worksheet "Regimen Archive"? Or is there a better method? When the Word reports are completed, printed, and verified, I owuld like a macro to copy all the reported data and append to this other sheet. I apologize, but I am a novice at this, as you can probably tell from my macro. The archive sheet is an exact copy of "Drug Regimen". No formatting of cells or anything.

Posted by Celia on December 08, 2000 2:29 AM

Re: Thanks for your help ....

By the way, could this be modified to append the data from Worksheet "Drug Regimen" to another Worksheet "Regimen Archive"? Or is there a better method? When the Word reports are completed, printed, and verified, I owuld like a macro to copy all the reported data and append to this other sheet. I apologize, but I am a novice at this, as you can probably tell from my macro. The archive sheet is an exact copy of "Drug Regimen". No formatting of cells or anything.

(8 responses) I have a column with either a value of 1 or blank. The sheet has a header. Is there code that can sort by value in column "Z", find the last

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View our Privacy Policy, Cookies Policy, and Terms of Use.