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

mstuf

Active Member
Joined
Feb 4, 2003
Messages
321
Office Version
  1. 2016
Platform
  1. Windows
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.
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
 
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

Code:
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 !
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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.
 
Upvote 0
Try:
Code:
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:
Code:
  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?
 
Last edited:
Upvote 0
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 --
 
Upvote 0
You could try something like:
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.
' 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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top