hi all
#1 does anybody know how to send selected rows to excel
for example i want to select certain customers from my list by holding down the Ctrl key or the shift key and send only those to excel?
i don't want to create a new list with only these customers
#2 is there a way to select 4 different random customers in my customer list and filter by these values or do i have-to manually enter the customer#|customer#| etc.
*This post is locked for comments
Actually this code below works even better.
It works even if the no. column is not the first column
Sub pasterowsfromnav()
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "@"
ActiveCell.Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Cells.EntireColumn.AutoFit
Cells.Select
Selection.NumberFormat = "General"
ActiveCell.Select
End Sub
Thanks for sharing, if your issue is resolved please mark it as verified.
I created this macro in excel, which solved the problem. With this macro you can place the rows where ever you want in excel, and just click on the macro, and it will paste the rows with the correct formatting and not touch other formatted cells in excel. This macro assumes your customer # or item no. or vendor no. is in the first row. This macro works like the paste function in excel if you have any other data in excel that is in the area where the rows will be pasted it will overwrite it!
Sub pasterowsfromnav()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.NumberFormat = "@"
ActiveCell.Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Cells.EntireColumn.AutoFit
ActiveCell.End(xlDown).Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.NumberFormat = "General"
ActiveCell.Select
End Sub
Right click on the column header you're pasting in ("B" if column B), then select "format cells" from the dropdown list. Under the Number tab, Category, choose "Text". Then paste into that column.
I’m using excel 2016. The random rows that you copied do any of your vendors no. start with a zero?
And if yes how do I set up excel by default to be a text format which will ultimately solve the problem
When I tried copying random rows of our vendor table, I just did the Ctrl click thing 3 times, right-clicked, copied rows, then opened up an Excel sheet, right clicked and pasted. All came across as text. In fact, when I right-clicked in Excel it only gave me the "A" paste option (text).
Your Excel must be 'special'.
I don't think setting on NAV will have any affect in this scenario, by default Excel has General Format so you have to change it manually in excel or check if there is any setting on excel which by default open in Text Format.
is there a way to format the no. Column in dynamics NAV, as a text format, so when you copy rows in NAV it is copying a text format to excel not a number format?
Format the column as Text before you paste the rows in excel and then paste the Rows.
yes you are right the standard excel export does not.But if i select certain rows and left click and select copy rows, when you paste it to excel, excel will trim the 0. Or maybe there is a excel macro that will do the pasting without trimming the zeros any help is appreciated.
Share
Report
All responses (
Answers (
47,773
