Hi! I am new to VBA and I have a question. I have the following code, with it to copy a table which has a header (A1 to F1) from each sheet (GestiuneCCS; AlimATM; RidDepAng) to a designated bookmark (1;2;3…) in Wd. It works “fine”, but in case I don’t have information in one table the macro brings in word whole table blank, and I don’t want that, I want it to bring only the Header of that table. Attach the macro. How can I correct the macro? Thanks
Please Login or Register to view this content.
Replace the part in red with the one below. Do the same for the other sheets. Untested.
Please Login or Register to view this content.Please Login or Register to view this content.
Click the * to say thanks.
Hi, Paul,
I have an "Run-time error "9" Subscript out of range on row LastCol = Sheets(“GestiuneSSC”).Range("A1").End(xlToRight).Column
Please help!
Also, from time to time I have an err "4601" Cannot activate appl, on row: .Activate ' make it the top pane, bring it to the front.
Attach a Excel sample containing data into those tables.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Hi, Paul, first of all, sorry for my late reply.
1/2) If I understand correctly, have attach the samples (Centralizare pt esantion si camere.xlsm - this is where I have macro) and how I want the final result in word (Final result), each table correspond to an inserted bookmark.
3) No confidential data in samples.
4) No merged cells.
Thanks again for all the help!
Last edited by Fane1981; 01-25-2019 at 04:37 AM. Reason: erase
I have no experience in word, so I have not replicated your dotm template. Try this as in your macro to copy the range until last not blank cell, assuming that your data range is into column A:G
Please Login or Register to view this content.
Last edited by PaulM100; 01-25-2019 at 05:41 AM.
On row: Sub ExportExcelDataToWordDocument()
Compile err: Expected End With
Updated in post #7, above in red
on row: With WB1.Sheets("GestiuneSSC")
Err "424" Object required
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
err on line: .Selection.GoTo what:=-1, Name:="bookmark1" ' -1 means "wdgotobookmark"
"438" object doesn't support this property or method.
This is how my macro look at this point
Please Login or Register to view this content.
Change this parttoPlease Login or Register to view this content.Please Login or Register to view this content.
Cross-posted at: https://www.mrexcel.com/forum/genera...es-word-4.html without any acknowledgement here of the help you've already been given there.
Also cross-posted at: https://www.excelforum.com/excel-pro...to-wd-vba.html
Evidently you don't care about the cross-posting rules here (http://www.excelforum.com/forum-rule...rum-rules.html) or on any other forum.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Macro, this is not cross-post, as you can see it is another macro, another issue.
For all practical purposes, it's essentially the same. Or are you just telling us you can't be bothered learning anything from the help you've been given elsewhere???
@mrexcel, for that macro I had no resolve.
So, please, can you help me with this one?!
Thank you!
Last edited by Fane1981; 01-25-2019 at 07:58 AM. Reason: add
@Paul:
Same error, same line.
Range("A1", "G" & LastRow).Select
Selection.Copy
Selection.GoTo what:=-1, Name:="bookmark1" ' -1 means "wdgotobookmark" >>>>THIS LINE
.Selection.Paste 'paste from the clipboard to the Word Doc.
I managed to make some changes to the macro, now it works for table 3, the one with no data in it, in word I have only the Header.
BUT, for the tables 1 an 2 (GestiuneSSC, AlimATM), with data in them, it copyies only the tables blank-no data.
@Paul/@Macro, Please help!
Part of macro with issues:
'as before, copy the whole table from sheet to clipboard.
Set WB = ThisWorkbook
With WB.Sheets("GestiuneSSC")
LastRow = .Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A1", "G" & LastRow).Select
Selection.Copy
wdWordApp.Selection.GoTo what:=-1, Name:="bookmark1" ' -1 means "wdgotobookmark"
wdWordApp.Selection.Paste 'paste from the clipboard to the Word Doc.
End With
'****************
'as before, copy the whole table from sheet to clipboard.
Set WB = ThisWorkbook
With WB.Sheets("AlimATM")
LastRow = .Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A1", "F" & LastRow).Select
Selection.Copy
wdWordApp.Selection.GoTo what:=-1, Name:="bookmark2" ' -1 means "wdgotobookmark"
wdWordApp.Selection.Paste 'paste from the clipboard to the Word Doc.
i made some trials on the macro, and I have other issue.
Now it copyies only the info from table1 (GestiuneSSC) - ONLY if I have some info in that tables (Table2 AlimATM&Table3 RisDepAngajati), into corresponding bookmarks (Bookmark2&Bookmark3).
If the tables AlimATM&RidDepAngajati are empty, then it copies only the Header in corresponding bookmarks.
If I have information in tables (AlimATM&RidDepAngajati), then it copies the data from table1 (GestiuneSSC), but only on the number of rows with info.
Help needed!
MACRO
'as before, copy the whole table from sheet to clipboard.
Set WB = ThisWorkbook
With WB.Sheets("GestiuneSSC")
LastRow = .Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A1", "F" & LastRow).Select
Selection.Copy
wdWordApp.Selection.GoTo what:=-1, Name:="bookmark1" ' -1 means "wdgotobookmark"
wdWordApp.Selection.Paste 'paste from the clipboard to the Word Doc.
End With
'****************
'as before, copy the whole table from sheet to clipboard.
Set WB = ThisWorkbook
With WB.Sheets("AlimATM")
LastRow = .Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A1", "F" & LastRow).Select
Selection.Copy
wdWordApp.Selection.GoTo what:=-1, Name:="bookmark2" ' -1 means "wdgotobookmark"
wdWordApp.Selection.Paste 'paste from the clipboard to the Word Doc.
End With
'****************
'as before, copy the whole table from sheet to clipboard.
Set WB = ThisWorkbook
With WB.Sheets("DepRidAngajati")
LastRow = .Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A1", "F" & LastRow).Select
Selection.Copy
wdWordApp.Selection.GoTo what:=-1, Name:="bookmark3" ' -1 means "wdgotobookmark"
wdWordApp.Selection.Paste 'paste from the clipboard to the Word Doc.
End With
Last edited by Fane1981; 01-25-2019 at 10:15 AM. Reason: ADD
Hi, Paul!
I managed to make the code to work. Thank you so, so much for all the support! Have a nice day!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks