# Add to Macro - Move additional Text from Excel to Word doc



## mstuf (May 22, 2014)

Hello - Thank you for looking.  I have a Working Macro that has saved 

me countless time with increased data accuracy across the last years that was created with Help of others more knowledgable than I. I am using - Windows XP SP3  &  Office XP and 2003.     

I would like to add a feature to this Macro that will add info to the 

end product making it more useable and again saving time.  

My Macro cuts one designated row from One Workbook and Places it in 

Another at the next available row position.  It then copies a specified cell and places the text in a Named Open Word Document at the position of the cursor.

I would like to Copy text from three additional Cells of that same 

last row and place them in another spot in the Open Word Document.  The additional text would all be placed on one line with space dash space between them/   The last row is currently left highlighted.  

Im envisioning that leaving the current macro as is and adding code to the end to return to the highlighted row to copy the cells I need  and then pasting them into the word document.

I'm puzzled as to how to specify the location that I want the 

Additional text copied to.    My thoughts say that the current code could be modified to leave the cursor at the desired position  ( its not the position of the previous paste )   - but unsure how to do that.   OR,  designating the position for the paste to be the first row after any row containing 6 or more dashes. ( -------- )  

The Text would be in Cells S , AQ  and AO of the last ( just 

transfered ) row of the Excel Workbook Named AMZ-GM Sold.xls.  

Here is my Current Code.   

```
Sub OpenToSold()
' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
  the Description at the Cursor in the open AmazonSale Word Doc.

   'Macro recorded 2/1/2008 by Mike

   ' Keyboard Shortcut: Ctrl+Shift+W

Dim lRow As Long
Dim lCol As Long
Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
ActiveSheet.Cells(lRow + 1, 1).Activate 'Add 1 to the last row for first blank row
ActiveSheet.Paste
Cells(ActiveCell.Row, 26).Copy ' Copy Z--- 26 = z



Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
WDApp.Selection.PasteSpecial
WDApp.Visible = True 'This should leave Word Open
' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
```

I'm getting better at editing my macros  but still having tough time with specifying variables / location.     There does not seem to be the pleathra of info for word VBA as there is for Excel.

Any Ideas or Help much appreciated.   Thank You


----------



## Macropod (May 22, 2014)

You could use code like:

```
Sub OpenToSold()
' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
  the Description at the Cursor in the open AmazonSale Word Doc.
' Keyboard Shortcut: Ctrl+Shift+W

Dim lRow As Long
Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
ActiveSheet.Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
With ActiveSheet.UsedRange
  lRow = .Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
  lRow = lRow + 1
  .Cells(lRow, 1).Paste
  WDApp.Bookmarks("first").Range.Text = .Cells(lRow, 10).Text
  WDApp.Bookmarks("second").Range.Text = .Cells(lRow, 20).Text
  WDApp.Bookmarks("third").Range.Text = .Cells(lRow, 26).Text
End With
WDApp.Visible = True 'This should leave Word Open
' Clean up
Set WDDoc = Nothing: Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
```
where the destination is a series of bookmarks; one per cell you want to output to Word. Note that, with this code, you don't need to have any particular range selected in Word.


----------



## mstuf (May 22, 2014)

> where the destination is a series of bookmarks; one per cell you want to output to Word. Note that, with this code, you don't need to have any particular range selected in Word.



Thanks Paul   --  attempted  but Received Compile Error at first .bookmarks statement    ( .bookmarks is Blocked in Dark Blue )  --statement says   "Method or Data Member not Found"

Really appreciate the help !


----------



## Macropod (May 22, 2014)

Oops, that should have been:
WDApp.ActiveDocument.Bookmarks ...
for each of those lines.


----------



## mstuf (May 22, 2014)

Macropod said:


> Oops, that should have been:
> WDApp.ActiveDocument.Bookmarks ...
> for each of those lines.



Thanks Again  -- The Macro now runs to completion.  The Initial transfer of the data in position 26 ( Cell Z )  transfers correctly to the position of the cursor in the word document   BUT  no text from Cells S  + AQ + AO  is copied.   Cell Z Remains copied to clipboard.  ( dotted border around it )  --  The position in the work document that I need the text from those cells pasted remains unchanged. 

Is there something I Can explain better to help convey the result I am seeking or show a copy of the word document I am using ?  

I will be away from the computer for about an hour.  I appreciate your efforts and will be available as soon as I return. 

Thanks Again


----------



## Macropod (May 22, 2014)

Ok, I'd missed your previous reference to Cells S + AQ + AO. Use:

```
WDDoc.Bookmarks("first").Range.Text = .Cells(lRow, 19).Text
  WDDoc.Bookmarks("second").Range.Text = .Cells(lRow, 26).Text
  WDDoc.Bookmarks("third").Range.Text = .Cells(lRow, 41).Text
  WDDoc.Bookmarks("fourth").Range.Text = .Cells(lRow, 43).Text
```

PS: What remains on the clipboard isn't just cell Z, but the whole row - from your previous cut/paste operation. The above code doesn't reference the clipboard; it references the worksheet directly and doesn't use copy/paste.


----------



## mstuf (May 22, 2014)

Thanks again   -- Hope you arent loosing patience -

Now the macro stops at   .Cells(lRow, 1).Paste    

Message = Run Time Error  438  -  Object doesnt support this property or method.  

AND another question / observation - 

Wont all the cells texts appear on the same line as the original paste the way its written ?  

Per OP: 


> "I would like to Copy text from three additional Cells of that same
> last row and place them in another spot in the Open Word Document. The additional text would all be placed on one line with space dash space between them.
> 
> Im envisioning that leaving the current macro as is and adding code to the end to return to the highlighted row to copy the cells I need and then pasting them into the word document.
> ...





The Place I need to paste the text is variable but always below the first paste.  Usually 8 - 10 Rows down depending on the content of the document.  I realize that its impossible to specify a constant location for it,  thus my idea that  -  The one thing that is Constant is that its always the first row following the first instance of 6  or more dashes.   (  ---------- )  

Hoping to see :

------------------
S - AQ - AO

Let me know if there is anything else I can explain better - Thank You Again.


----------



## Macropod (May 23, 2014)

> Now the macro stops at .Cells(lRow, 1).Paste
> 
> Message = Run Time Error 438 - Object doesnt support this property or method.


I have no idea why you're now getting the Run Time Error 438 error - it wasn't occurring before and changing the code to populate the Word document has nothing to do with that part of the code.


> Wont all the cells texts appear on the same line as the original paste the way its written ?


I'm not sure what you mean. The code as I've revised it finds the last row in the destination workbook, increments the count by one, then uses the new address for all further operations - pasting & getting data for the Word document.





> The Place I need to paste the text is variable but always below the first paste.


The code I've provided assumes there are always four known locations in the document where the data need to go, each of which is identified by a bookmark there. Thus, it doesn't matter what you do/don't have selected. If that isn't how your document works (or can work), then you're going to have explain how the macro is supposed to know where all the other data go. Simply saying there's 6 or more dashes might work for the first one, but what about the rest? And, if you can have 6 or more dashes there, why not a bookmark?


----------



## mstuf (May 23, 2014)

Macropod said:


> I have no idea why you're now getting the Run Time Error 438 error - it wasn't occurring before and changing the code to populate the Word document has nothing to do with that part of the code.
> 
> Yes - Did not happen before -- Is there anything I can post that may help see it ?     Maybe a new copy of the code as I have it after pasting in the new sections ?
> 
> ...


----------



## Macropod (May 23, 2014)

Re Run Time Error 438, I suggest you check what else has changed about your workbooks (e.g. which one is active when you run the macro).


----------



## mstuf (May 22, 2014)

Hello - Thank you for looking.  I have a Working Macro that has saved 

me countless time with increased data accuracy across the last years that was created with Help of others more knowledgable than I. I am using - Windows XP SP3  &  Office XP and 2003.     

I would like to add a feature to this Macro that will add info to the 

end product making it more useable and again saving time.  

My Macro cuts one designated row from One Workbook and Places it in 

Another at the next available row position.  It then copies a specified cell and places the text in a Named Open Word Document at the position of the cursor.

I would like to Copy text from three additional Cells of that same 

last row and place them in another spot in the Open Word Document.  The additional text would all be placed on one line with space dash space between them/   The last row is currently left highlighted.  

Im envisioning that leaving the current macro as is and adding code to the end to return to the highlighted row to copy the cells I need  and then pasting them into the word document.

I'm puzzled as to how to specify the location that I want the 

Additional text copied to.    My thoughts say that the current code could be modified to leave the cursor at the desired position  ( its not the position of the previous paste )   - but unsure how to do that.   OR,  designating the position for the paste to be the first row after any row containing 6 or more dashes. ( -------- )  

The Text would be in Cells S , AQ  and AO of the last ( just 

transfered ) row of the Excel Workbook Named AMZ-GM Sold.xls.  

Here is my Current Code.   

```
Sub OpenToSold()
' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
  the Description at the Cursor in the open AmazonSale Word Doc.

   'Macro recorded 2/1/2008 by Mike

   ' Keyboard Shortcut: Ctrl+Shift+W

Dim lRow As Long
Dim lCol As Long
Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
ActiveSheet.Cells(lRow + 1, 1).Activate 'Add 1 to the last row for first blank row
ActiveSheet.Paste
Cells(ActiveCell.Row, 26).Copy ' Copy Z--- 26 = z



Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
WDApp.Selection.PasteSpecial
WDApp.Visible = True 'This should leave Word Open
' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
```

I'm getting better at editing my macros  but still having tough time with specifying variables / location.     There does not seem to be the pleathra of info for word VBA as there is for Excel.

Any Ideas or Help much appreciated.   Thank You


----------



## mstuf (May 23, 2014)

The Workbooks have no changes --  they have not even been closed since first attempt that ran OK.  

Here is Code as I have it now  


```
Sub OpenToSold4()
' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
  the Description at the Cursor in the open AmazonSale Word Doc.
' Keyboard Shortcut: Ctrl+Shift+W

Dim lRow As Long
Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
ActiveSheet.Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
With ActiveSheet.UsedRange
  lRow = .Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
  lRow = lRow + 1
  .Cells(lRow, 1).Paste
  WDDoc.Bookmarks("first").Range.Text = .Cells(lRow, 19).Text
  WDDoc.Bookmarks("second").Range.Text = .Cells(lRow, 26).Text
  WDDoc.Bookmarks("third").Range.Text = .Cells(lRow, 41).Text
  WDDoc.Bookmarks("fourth").Range.Text = .Cells(lRow, 43).Text
End With
WDApp.Visible = True 'This should leave Word Open
' Clean up
Set WDDoc = Nothing: Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
```

Thanks again Paul  !


----------



## Macropod (May 23, 2014)

The fact you haven't closed anything may be part of the problem, especially with the 'ActiveSheet.Rows(ActiveCell.Row).Cut' line - by now there probably won't be anything there to cut & paste to a new last row in the destination workbook. Otherwise, the code looks OK.


----------



## mstuf (May 23, 2014)

Macropod said:


> The fact you haven't closed anything may be part of the problem, especially with the 'ActiveSheet.Rows(ActiveCell.Row).Cut' line - by now there probably won't be anything there to cut & paste to a new last row in the destination workbook. Otherwise, the code looks OK.




Shut down Excel and Word and Restarted  -- Same Issue.  

With my Original Macro I only shut down every 7 days. I run the original Macro 15 to 60 Times a day 5 days a week.  

From reading about Bookmarks it appears that they would work if my document were a Template.  I see how to add ( set ) them but it appears that each time a new document is opened they have to be re added.   My imported order info does not go into a template as it can contain different volumes of info depending on number of lines in the address - lines in the product name etc.     Each Order is a New Doc.  

Heres my Process  -  maybe it will help understand -  

     I receive order information in a Excel Text File that I run a Macro on that Re arranges the info for each row in Cell T.  I paste it into a word document that Has only my Return Address on the top left corner.  I paste the info from the order sheet in and run a macro that Harvests the address  - moves it to the top under the Return address and Increases the size - making a shipping label  -- the next block created gives a packing list for the buyer  and the Last Section ( under the line of  -------s ) is info for me with Product title  _ SKU  - Order Number - buyer info etc.   All on one sheet  -- Its pretty slick.  The Row of ---------s is added manually.  

The Macro we are working on is then run with the cursor in a set position -   it removes the inventory row for the item from Excel in Stock Stuff to Excel Sold Stuff and pastes the description ( Cell Z ) that was shown onsite into the buyers packing list section at the cursor.   


After Printing -- I close the Sale doc -- I do not save - and re open the Sale Doc and begin again.    Sounds Complex but with my keystroke shortcuts and having used it 1000's of times, each order takes way less than a minute to process.  I normally can beat the rate that my printer prints the previous one sheet.  I can fly though it.  

SO I'm wondering ...  

Is there a way to Find and Name the first instance of at least  ------  ( 6 dashes -  Sometimes there are more but never less than 6 that denote the location needed )   as an object or named location then specify next line as a place to print text from  Cells S + AQ + AO of the previously  Cut and pasted  still highlighted last row of the Sold File   with /s  or  Dashs between them ?    Seems maybe I could go back to my original macro that works -  and add code to end to do so ??  

Thanks for bearing with me.  At 62 I struggle with new stuff.


----------



## Macropod (May 23, 2014)

Try:

```
With ActiveSheet
  lRow = .UsedRange.Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
  lRow = lRow + 1
  .Paste .Cells(lRow, 1)
```

Sure, you could use the Find method in Word vba to locate the dashes, but that's much less straightforward than using bookmarks and, if you revert to something along the lines of the previous code, you're still stuck with having to select the destination for the first paste. If, as your last post seems to suggest, the S, AQ & AO contents are to be output as S - AQ - AO, you only need a single bookmark, for that. For example:

```
WDDoc.Bookmarks("first").Range.Text = .Cells(lRow, 26).Text
  WDDoc.Bookmarks("second").Range.Text = .Cells(lRow, 19).Text & _
    " - " & .Cells(lRow, 43).Text & " - " & .Cells(lRow, 41).Text
```

As a retiree myself, I understand the struggle with new stuff - tried learning any languages lately?


----------



## mstuf (Jun 9, 2014)

> Sure, you could use the Find method in Word vba to locate the dashes, but that's much less straightforward than using bookmarks



HI PAUL  -   Thanks for all your effort   -- 

I have spent some time learning what I can about Bookmarks.  I do have another routine of item creation that the new knowledge will work nicely with  -- Thank You ! 

I was able, with the last change, to run the macro.   I can make the bookmarks work by creating a template but my Order info cannot be applied to a template without a great deal of backwash.   Variations such as Sales of Different Item Types  -- International Orders  etc cause placement of needed other details to be different.   Some Manual Editing after basic layout will always be required.    I did not mention it previously but I use the same Macro and other set up macros to print orders for other venues as well.    The Variety of uses for this document and Macro make total automation impossible for me.  
Trying to change to a template so that I can use the bookmarks would cause need for changes to macros several steps back in my Spaghetti Web of Macros and Routines and then require more editing than I currently do.
As You Mentioned – it would be possible to not have to even manually locate the first paste but, after doing the editing I do anyway leaving the cursor at the spot needed in just a habit after the thousands and thousands of times I have used the Macro / Routine. 

SO ? …    I’m back to the Opening Post.  ---  Would you still be willing to help me to  --   

Add to my current code to then Locate the Cursor in the Active Word Document at the beginning of the First Row after the first occurrence of  6 or more Dashes  - return to the recently pasted row in AMZ-GM Sold.xls ( which is still highlighted )   - Copy the text of Cells S, AQ, and AO  and paste them into my Active Word Document   as  S – AQ – AO  -  leaving it the visible document ?  

Thanks Again for all the effort  --


----------



## Macropod (Jun 10, 2014)

You could try something like:

```
Sub OpenToSold()
' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
  the Description at the Cursor in the open AmazonSale Word Doc.
' Keyboard Shortcut: Ctrl+Shift+W

Dim lRow As Long
Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
ActiveSheet.Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
With ActiveSheet.UsedRange
  lRow = .Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
  lRow = lRow + 1
  .Cells(lRow, 1).Paste
  With WDDoc
    Selection.Text = ActiveSheet.Cells(lRow, 26).Text
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Forward = True
      .Wrap = 0
      .Format = False
      .MatchWildcards = True
      .Text = "[\-]{6,}"
      .Replacement.Text = ActiveSheet.Cells(lRow, 19).Text _
        & " - " & ActiveSheet.Cells(lRow, 41).Text _
        & " - " & ActiveSheet.Cells(lRow, 43).Text
      .Execute 1
    End With
  End With
End With
WDApp.Visible = True 'This should leave Word Open
' Clean up
Set WDDoc = Nothing: Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
```


----------



## mstuf (Jun 11, 2014)

Hi Paul   -- Thanks for taking the challenge on.

I spent a day trying to familiarize myself with some of the new code commands.   Very Interesting.

I ran an attempt  and  received a Run Time Error 438 - Object doesnt support property of method  --  at  1Row = 1Row + 1.

I have read through some threads here about Error 438   and Microsoft help  but I cannot see the issue with my knowledge and understanding.  

Trying to grasp the changes to the initial code previous to the error but having a hard time understanding UsedRange.

Thanks again


----------



## Macropod (Jun 11, 2014)

Since that part of the code is unchanged from what worked previously, I suspect the problem has something to do with your data and/or that a re-start of office might be needed.


----------



## mstuf (Jun 29, 2014)

Macropod said:


> Since that part of the code is unchanged from what worked previously, I suspect the problem has something to do with your data and/or that a re-start of office might be needed.




Sorry to have been so Long Getting back to this  -- We had a parental health issue here that took precedence.  

My Reference to "working" was to the Bookmark code --  I wanted to learn about them and made them work before realizing the template issue.    I have not been able to make the Last Full Code work.

Even after restart --  I still got "Run Time Error 438 Object does not support this Property or Method"   at  ".Cells(lRow, 1).Paste"

SO...

In the last weeks while away from home,  I have been making attempts myself and have got close.   The Code below Accomplishes the basic task but One item needs help to finish.  

My Copy and Paste of S AQ AO  ( 19, 43, 41 ) is appearing in my Word Document as 

S   
AQ
AO

each are on a seperate line. 

and I'm hoping for   S - AQ - AO  all on Same Line.  

Not sure I have done it the best way but i'm close and it does work.  


```
Sub OpenToSoldA()
' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
  the Description at the Cursor in the open AmazonSale Word Doc.

   'Macro recorded 2/1/2008 by Mike

   ' Keyboard Shortcut: Ctrl+Shift+W

Dim lRow As Long
Dim lCol As Long
Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row 'This gets the last row from Sold.xls
ActiveSheet.Cells(lRow + 1, 1).Activate 'Add 1 to the last row for first blank row
ActiveSheet.Paste
Cells(ActiveCell.Row, 26).Copy ' Copy Z--- 26 = z


Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
WDApp.Selection.PasteSpecial
   
WDApp.Visible = True 'This should leave Word Open


With WDApp.Selection.Find
    .Text = "------"
           .Execute
End With

WDApp.Selection.MoveDown Unit:=wdLine, Count:=1

Windows("AMZ-GM Sold.xls").Activate
Cells(ActiveCell.Row, 19).Copy

WDApp.Selection.PasteSpecial
WDApp.Visible = True


Windows("AMZ-GM Sold.xls").Activate
Cells(ActiveCell.Row, 43).Copy

WDApp.Selection.PasteSpecial
WDApp.Visible = True


Windows("AMZ-GM Sold.xls").Activate
Cells(ActiveCell.Row, 41).Copy

WDApp.Selection.PasteSpecial
WDApp.Visible = True

  Set WDDoc = Nothing: Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
```

Unsure weather to try for Code to rearrange the pasted Text  or if its best to try to revise the Excel code to paste it properly. 

I've learned so much in the last few weeks - But I'm Not finding many previous threads or Code Examples that cover this last issue of arranging Excel Text pasted to a Word Document.   

 Thank you again.


----------



## Macropod (Jun 29, 2014)

> I still got "Run Time Error 438 Object does not support this Property or Method" at ".Cells(lRow, 1).Paste"


Way back in post #5 you said code with that line worked fine. I now see that it doesn't and needs to be:
.Paste Destination:=.Cells(lRow, 1)
With that change, the code in post #16 should work.


----------



## mstuf (May 22, 2014)

Hello - Thank you for looking.  I have a Working Macro that has saved 

me countless time with increased data accuracy across the last years that was created with Help of others more knowledgable than I. I am using - Windows XP SP3  &  Office XP and 2003.     

I would like to add a feature to this Macro that will add info to the 

end product making it more useable and again saving time.  

My Macro cuts one designated row from One Workbook and Places it in 

Another at the next available row position.  It then copies a specified cell and places the text in a Named Open Word Document at the position of the cursor.

I would like to Copy text from three additional Cells of that same 

last row and place them in another spot in the Open Word Document.  The additional text would all be placed on one line with space dash space between them/   The last row is currently left highlighted.  

Im envisioning that leaving the current macro as is and adding code to the end to return to the highlighted row to copy the cells I need  and then pasting them into the word document.

I'm puzzled as to how to specify the location that I want the 

Additional text copied to.    My thoughts say that the current code could be modified to leave the cursor at the desired position  ( its not the position of the previous paste )   - but unsure how to do that.   OR,  designating the position for the paste to be the first row after any row containing 6 or more dashes. ( -------- )  

The Text would be in Cells S , AQ  and AO of the last ( just 

transfered ) row of the Excel Workbook Named AMZ-GM Sold.xls.  

Here is my Current Code.   

```
Sub OpenToSold()
' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
  the Description at the Cursor in the open AmazonSale Word Doc.

   'Macro recorded 2/1/2008 by Mike

   ' Keyboard Shortcut: Ctrl+Shift+W

Dim lRow As Long
Dim lCol As Long
Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
ActiveSheet.Cells(lRow + 1, 1).Activate 'Add 1 to the last row for first blank row
ActiveSheet.Paste
Cells(ActiveCell.Row, 26).Copy ' Copy Z--- 26 = z



Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
WDApp.Selection.PasteSpecial
WDApp.Visible = True 'This should leave Word Open
' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing
Application.WindowState = xlMinimized
End Sub
```

I'm getting better at editing my macros  but still having tough time with specifying variables / location.     There does not seem to be the pleathra of info for word VBA as there is for Excel.

Any Ideas or Help much appreciated.   Thank You


----------



## mstuf (Jun 29, 2014)

Macropod said:


> With that change, the code in post #16 should work.



Same 438 Error Message  -  

Its 3:30 AM Here  -- First Night Home   --  I'm hitting the bed for tonight   but back at it tomorrow  --  

Thanks For Looking again


----------



## mstuf (Jul 13, 2014)

Paul  - I want to thank you so much  -   I have made something work. It may not be the best way but it works.  I'm so pleased.   
During the Process I learned about Bookmarks -  Using Find Via VBA -  Execute  -  EndKey and more that I had not used before.  
I will be able to use bookmark in another application that will save much time.
Best part is, I think I understand  everything but "Set" - Still working on that.   
Never could get past that 438 Error message with your direction so I proceeded with my probably improper idea and have made it work.  Also learned a lot about re arranging text in Word with VBA.

Heres what I arrived at - for anyone following who is interested. 


```
Sub OpenToSold5()

Dim lRow As Long
Dim lCol As Long
Rows(ActiveCell.Row).Cut
Windows("AMZ-GM Sold.xls").Activate
lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row 
ActiveSheet.Cells(lRow + 1, 1).Activate 
ActiveSheet.Paste
Cells(ActiveCell.Row, 26).Copy ' Copy Z--- 26 = z


Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Set WDApp = GetObject(, "Word.Application") ' Reference active document
Set WDDoc = WDApp.ActiveDocument
WDApp.Selection.PasteSpecial
   
WDApp.Visible = True 

With WDApp.Selection.Find
    .Text = "------"
           .Execute
End With

WDApp.Selection.MoveDown Unit:=wdLine, Count:=1

Windows("AMZ-GM Sold.xls").Activate
Cells(ActiveCell.Row, 19).Copy

WDApp.Selection.PasteSpecial
WDApp.Visible = True

WDApp.Selection.MoveUp Unit:=wdLine, Count:=1
WDApp.Selection.EndKey Unit:=wdLine
WDApp.Selection.TypeText Text:="   -   "

Windows("AMZ-GM Sold.xls").Activate
Cells(ActiveCell.Row, 43).Copy

WDApp.Selection.PasteSpecial
WDApp.Visible = True

WDApp.Selection.TypeText Text:="   -   "

Windows("AMZ-GM Sold.xls").Activate
Cells(ActiveCell.Row, 41).Copy

WDApp.Selection.PasteSpecial
WDApp.Visible = True 

' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing
Application.WindowState = xlMinimized

End Sub
```

My Document appears ready to print with my 3 extra pieces of information included where I wanted them.

Again thanks so much for taking this on  --  I know that not a lot of folks understand Combined Excel Word Macros. Very Nice of you to share your knowledge.


----------

