1 我係用緊excel 365, 但excel 2016 同樣可以做到。 2 除藍色copy and past 嘅圖像，其餘所有都excel 操作嘅畫面

@@abellee9120 Hi Abel, 多謝你嘅提議，會安排你嘅要求。

@@KKLeung 可以的話+介紹Pivotby, 謝謝

@@pychow5666 Hi Vivian, thanks for watching.

@@Iam_ShineMind 多謝你嘅好評，協助轉發俾有需要嘅朋友，就係對本平台嘅義工最大嘅支持🙏😀

@@hst7664 多謝你嘅提問 當你選擇由sheet2 轉去sheet1, 在VBA 語言為之activate sheet1

Hi Jane , Thanks for watching

@@nelsonnelson1044 hi Nelson, thanks for watching

@@nelsonnelson1044 Thanks for watching

@@shasashasa8926 多謝收看, power query 是可以做到綜合分析。

請問所有excel files 嘅結構都是一樣?

​​@@KKLeungalmost the same, but each file have large data

@@VincentLui-kt7dw Hi Vincent, 沒有你所述試題，但有其他公司類似嘅問題，請參考下面 video kzbin.info/www/bejne/e3-cd3h8erqUmqcsi=-bT9LO2O4bhyEASw kzbin.info/www/bejne/Znu0h2OsnZJ-nsksi=DHLKit3eRN4o--zd

@KKLeung 多謝，希望你可以拍下，我見youtube 好似冇咩呢D片。

Hi Joanna, please refer the video below. It could provide you the solution. Thanks, KK kzbin.info/www/bejne/g2Otcox8odaopJosi=4OvXcvKVxEUA0KNb

@@panwong8936 Hi Pan, 多謝你嘅好評，協助轉發俾有需要嘅朋友，就係對本平台嘅義工最大嘅支持🙏😀

Thanks for watching! 😊

Thanks for watching.

如果寬度足夠文字依然轉到第二項 在這情況, 可按下列步驟免除轉到第二項, 如有問題再留言交流 1 將 Formula Bar 拉下 2 cursor擺在December前面 3 按 Backspace

@@KKLeung Step 3 按 Backspace會delete 31和December之間空格？ 我想補充 這段文字是在合併儲存格 和 左右對齊。 我嘗試過在31 December 前按ALT ENTER﹐是會(強行)跳到下一行。但第一行的文字做不到左右對齊。 Thanks!😇

@@hwtang1985 ） 如31 同December 未有空位，只需按 space bar 就可以 2） 左右相齊, 你可在 2.1 format cell 2.2 選擇Alignment 2.3 在Horizontal 選擇 2.4 Distributed (indent)

@@KKLeung 好像不行，不知是否EXCEL版本問題 我也上網找了一些資料 在office word 中，如遇到日子分開了，可以使用non-breaking space (即ctrl + shift + space) 我猜這non-breaking space是將 31 December 2023當作為一組字，我也在word 成功做到，不會因為自動分行，而斷開顯示。 但我在excel 是用不到這方法， Anyway, 感謝你的解答🥰🥰🥰👍

@@hwtang1985 多謝交流你嘅經驗。

@@paulyeung5628 Hi Paul, 多謝收看😁

@@thesonofbighon5168 多謝收看。

Hi Howard, 多謝你提供嘅方法。

@@mangwacraterchan3877 多謝你嘅好評，協助轉發俾有需要嘅朋友，就係對本平台嘅義工最大嘅支持🙏😀

@@AA-ev3vy 多謝收看

請參考下列影片可以幫肋你明白你嘅提問 kzbin.info/www/bejne/pqq6YpSqr52kf6csi=6vPrG5NWcwbVGHin

@@KKLeung 條link唔係關於excel？？

@@manchunyuen9437 So sorry, you are right. I will send the link later👌

@@manchunyuen9437 please try the link below kzbin.info/www/bejne/fnaoc6GsmamUps0

更新資料後將兩個文檔close. 再打開pivot table 文檔，cursor 在pivot table 內，fresh data 就可以。應該唔會有問題。如有問題再留言交流下。

@@加油好運 多謝收看 如果你見到program 頂部有Option Explicit 存在，請嘗試除去 Option Explicit 因為有這句statement, 所有program 必要定義 variable。 再有問題, 歡迎留言大家再交流一下。

@@KKLeung 謝謝KK SIR, 那OPTION EXPLICIT已搞掂, 但係唔知點解就算成個CODE COPY去都RUN不到, 佢冇顯示出錯, 係向append survey個表到出唔到資料...

@@加油好運 現正外出旅遊，要到30號才回來工作，遲啲回你。

@@KKLeung 謝謝KK SIR, enjoy your trip!　我已經搞掂左，　冇問題啦！

@@加油好運 太好了👍

Hi Louis, 多謝收看。 現正外出旅遊，要到30 October 才回工作，要遲啲先至回覆。

Thanks and have a nice trip !

Hi Louis, I cannot find a computer installed with Excel 2013. But you may try as below. But I am not sure it works or not. Insert tab > My Add-ins or Store

@@siulingying1034 多謝收看

@@Teen1-vi1rj Thanks for watching

@@SSFwong-p6f 多謝你的好評。

@@siulingng1991 多謝收看，有時間就會多做一啲😁

@@siulingng1991 多謝收看😁，一有時間就做多啲👌

@@michelleyeung4273 Hi Michelle, 多謝你嘅好評，協助轉發俾有需要嘅朋友，就係對本平台嘅義工最大嘅支持🙏😀

@@ansonchew6345 Hi Anson, 多謝你嘅好評，協助轉發俾有需要嘅朋友，就係對本平台嘅義工最大嘅支持🙏😀

@@加油好運 多謝你嘅好評，協助轉發俾有需要嘅朋友，就係對本平台嘅義工最大嘅支持🙏😀

@@KKLeung 一定

@@user-wd6iu2ec3k 多謝你嘅好許，有較多資源係會加上字幕 。

Thank you for watching 😊

Thanks for watching. File download is available now.

So useful analysing method for beginner, thanks KK!

@@pollychua4545 Hi Polly, Thanks for watching

@@heiaheiaheiahei thank you very much 👍

@@heiaheiaheiahei 多謝你嘅好評，協助轉發俾有需要嘅朋友，就係對本平台嘅義工最大嘅支持🙏😀

@@youtubeandkk 多謝你嘅提問，你是否希望將相同工序嘅收入同支出做個差異計算？

@@KKLeung 無錯 若果可以，仲想需要時顯示明細，未知是否可行？ 謝謝你

按這個情況，應該用append query 將兩個data set 整合做分析。你試吓是否可以行。

@@KKLeung THX 我研究下先

多謝你嘅建議，真係要待有時間才可以安排。

@@KKLeung 不急，唔該晒KK sir🙏

@@soniaaw9289 Hi Sonia, 多謝收看，我很久沒有用Mac, 你可以試吓option key。

Hi Nana, 請問你是什麼版本Excel?

Hi Candy, 多謝你嘅提問。會按排時間研究下，遲啲回覆你。

Hi Candy, 請使用下面vba code 及留意Cell H3 如何加入多個文檔路徑 主要變更： 1 將單一的 AttachmentPath 變數替換為一個陣列 AttachmentPaths()。 2 使用 Split 函數來分隔 H3 單元格中的多個檔案路徑。這些路徑應該在單元格中以分號（;）分隔。 3 添加了一個迴圈，用於遍歷每個附件路徑，並在該路徑不為空時將其添加到電子郵件中。 Sub Email_table_mult_Attachment() Dim outlook As Object 'for outlook application Dim newEmail As Object 'for outlook function Dim Inspect As Object 'for outlook inspector function Dim Page_Editor As Object 'for outlook WordEditor function Dim ws As Worksheet Dim AttachmentPaths() As String Dim i As Long Set outlook = CreateObject("Outlook.Application") ' Create a new instance of Outlook Set newEmail = outlook.CreateItem(0) ' Create a new email Set ws = ActiveSheet With newEmail .To = ws.Range("C3") .CC = ws.Range("D3") .BCC = ws.Range("E3") .Subject = ws.Range("F3") .Body = ws.Range("G3") & vbCrLf & " " ' Get attachment paths from cell H3 and split them AttachmentPaths = Split(ws.Range("H3").Value, ";") ' Loop through each attachment path and add it to the email For i = LBound(AttachmentPaths) To UBound(AttachmentPaths) If Trim(AttachmentPaths(i)) <> "" Then .Attachments.Add Trim(AttachmentPaths(i)) End If Next i .display ' Display the email item for manual review and sending Set Inspect = newEmail.GetInspector ' Get the inspector object for the email Set Page_Editor = Inspect.WordEditor ' Get the Word editor object for the email body ' Set the start and end positions of the selection in the email body Page_Editor.Application.Selection.Start = Len(.Body) Page_Editor.Application.Selection.End = Page_Editor.Application.Selection.Start ' Copy the range "TableX[#All]" on the active sheet ws.Range("TableX[#All]").Copy ' Paste the copied range as plain text into the email body Page_Editor.Application.Selection.PasteAndFormat (wdFormatPlainText) ' Insert a new paragraph in the email body Page_Editor.Application.Selection.TypeParagraph ' Add a closing remark and sender name below the pasted table Page_Editor.Application.Selection.TypeText "Regards" & vbCrLf & "Sender Name " 'User changes the name ' Clean up - release the editor and inspector objects Set Page_Editor = Nothing Set Inspect = Nothing '.send 'Send with preview in outlook End With ' Clean up - release the email and Outlook objects Set newEmail = Nothing Set outlook = Nothing End Sub If I need to send more than one attached file, how to change the VBA to the requirment Sub Email_table() Dim outlook As Object 'for outlook application Dim newEmail As Object 'for outlook function Dim Inspect As Object 'for outlook inspector function Dim Page_Editor As Object 'for outlook WordEditor function Dim ws As Worksheet Dim AttachmentPaths As String Dim AttachmentArray() As String Dim i As Long Dim FilePath As String Set outlook = CreateObject("Outlook.Application") ' Create a new instance of Outlook Set newEmail = outlook.CreateItem(0) ' Create a new email Set ws = ActiveSheet With newEmail .To = ws.Range("C3") .CC = ws.Range("D3") .BCC = ws.Range("E3") .Subject = ws.Range("F3") .Body = ws.Range("G3") & vbCrLf & " " ' Get attachment paths from cell H3 AttachmentPaths = ws.Range("H3").Value ' Split the paths into an array If Len(Trim(AttachmentPaths)) > 0 Then AttachmentArray = Split(AttachmentPaths, ";") ' Loop through each attachment path and add it to the email For i = LBound(AttachmentArray) To UBound(AttachmentArray) FilePath = Trim(AttachmentArray(i)) If Len(FilePath) > 0 Then ' Remove "??" prefix if present If Left(FilePath, 2) = "??" Then FilePath = Mid(FilePath, 3) End If On Error Resume Next .Attachments.Add FilePath If Err.Number <> 0 Then MsgBox "Failed to attach file: " & FilePath, vbExclamation Err.Clear End If On Error GoTo 0 End If Next i End If .display ' Display the email item for manual review and sending Set Inspect = newEmail.GetInspector ' Get the inspector object for the email Set Page_Editor = Inspect.WordEditor ' Get the Word editor object for the email body ' Set the start and end positions of the selection in the email body Page_Editor.Application.Selection.Start = Len(.Body) Page_Editor.Application.Selection.End = Page_Editor.Application.Selection.Start ' Copy the range "TableX[#All]" on the active sheet ws.Range("TableX[#All]").Copy ' Paste the copied range as plain text into the email body Page_Editor.Application.Selection.PasteAndFormat (wdFormatPlainText) ' Insert a new paragraph in the email body Page_Editor.Application.Selection.TypeParagraph ' Add a closing remark and sender name below the pasted table Page_Editor.Application.Selection.TypeText "Regards" & vbCrLf & "KK " 'User changes the name ' Clean up - release the editor and inspector objects Set Page_Editor = Nothing Set Inspect = Nothing '.send 'Send with preview in outlook End With ' Clean up - release the email and Outlook objects Set newEmail = Nothing Set outlook = Nothing End Sub Sub Email_table() Dim outlook As Object 'for outlook application Dim newEmail As Object 'for outlook function Dim Inspect As Object 'for outlook inspector function Dim Page_Editor As Object 'for outlook WordEditor function Dim ws As Worksheet Dim AttachmentPaths As String Dim AttachmentArray() As String Dim i As Long Dim FilePath As String Set outlook = CreateObject("Outlook.Application") ' Create a new instance of Outlook Set newEmail = outlook.CreateItem(0) ' Create a new email Set ws = ActiveSheet With newEmail .To = ws.Range("C3") .CC = ws.Range("D3") .BCC = ws.Range("E3") .Subject = ws.Range("F3") .Body = ws.Range("G3") & vbCrLf & " " ' Get attachment paths from cell H3 AttachmentPaths = ws.Range("H3").Value ' Split the paths into an array If Len(Trim(AttachmentPaths)) > 0 Then AttachmentArray = Split(AttachmentPaths, ";") ' Loop through each attachment path and add it to the email For i = LBound(AttachmentArray) To UBound(AttachmentArray) FilePath = Trim(AttachmentArray(i)) If Len(FilePath) > 0 Then ' Remove "??" prefix if present If Left(FilePath, 2) = "??" Then FilePath = Mid(FilePath, 3) End If On Error Resume Next .Attachments.Add FilePath If Err.Number <> 0 Then MsgBox "Failed to attach file: " & FilePath, vbExclamation Err.Clear End If On Error GoTo 0 End If Next i End If .display ' Display the email item for manual review and sending Set Inspect = newEmail.GetInspector ' Get the inspector object for the email Set Page_Editor = Inspect.WordEditor ' Get the Word editor object for the email body ' Set the start and end positions of the selection in the email body Page_Editor.Application.Selection.Start = Len(.Body) Page_Editor.Application.Selection.End = Page_Editor.Application.Selection.Start ' Copy the range "TableX[#All]" on the active sheet ws.Range("TableX[#All]").Copy ' Paste the copied range as plain text into the email body Page_Editor.Application.Selection.PasteAndFormat (wdFormatPlainText) ' Insert a new paragraph in the email body Page_Editor.Application.Selection.TypeParagraph ' Add a closing remark and sender name below the pasted table Page_Editor.Application.Selection.TypeText "Regards" & vbCrLf & "KK " 'User changes the name ' Clean up - release the editor and inspector objects Set Page_Editor = Nothing Set Inspect = Nothing '.send 'Send with preview in outlook End With ' Clean up - release the email and Outlook objects Set newEmail = Nothing Set outlook = Nothing End Sub

Thxs, K K Sir, I will try and give feedback to you for the result.

@@candyng8169 可以呀

Hi Hudson, 多謝你嘅提問 Custom list 確實係有數目上限, 我試過不同嘅內容, 超過165行就會開始出現錯誤, 所以建議不超過20行嘅資料比較穩妥. 真的需要自動排出多項資料, 可能要用vba解決方案

@@LeonaTheLion117 多謝收看

@@TrinaLam-h5z Hi Trina, 多謝收看。我未有使用過Sharepoint。不敢肯定可否連接。