跳到主要內容

如何僅在Excel中將值粘貼到可見/過濾的單元格?

在Excel中,如果您已過濾數據或隱藏了某些單元格,並且在將值粘貼到過濾或隱藏範圍內時,隱藏的單元格也會被粘貼的值填充。 在這裡,我將告訴您一些技巧,僅將值粘貼到可見單元格中。

使用VBA將值粘貼到可見單元格

通過粘貼到Kutools for Excel的可見範圍將值粘貼到可見單元格 好主意3


使用VBA將值粘貼到可見單元格

在Excel中,除了VBA,沒有其他方法可以解決此問題。

1。 按 Alt + F11鍵 同時輸入一個鍵 Microsoft Visual Basic for Applications 窗口彈出。

2。 點擊 插入 > 模塊,然後將以下VBA代碼粘貼到彈出的“模塊”窗口中。

VBA:僅將值粘貼到可見單元格。

Sub CopyFilteredCells()
	'Updateby20150203
	Dim rng1 As Range
	Dim rng2 As Range
	Dim InputRng As Range
	Dim OutRng As Range
	xTitleId     = "KutoolsforExcel"
	Set InputRng = Application.Selection
	Set InputRng = Application.InputBox("Copy Range :", xTitleId, InputRng.Address, Type: = 8)
	Set OutRng   = Application.InputBox("Paste Range:", xTitleId, Type: = 8)
	For Each rng1 In InputRng
		rng1.Copy
		For Each rng2 In OutRng
			If rng2.EntireRow.RowHeight > 0 Then
				rng2.PasteSpecial
				Set OutRng = rng2.Offset(1).Resize(OutRng.Rows.Count)
				Exit For
			End If
		Next
	Next
	Application.CutCopyMode = False
End Sub

3。 點擊 F5 鍵或 按鈕,然後會彈出一個對話框供您選擇要復制的值。 看截圖:
文檔粘貼到可見單元格 1

4.選擇值後,單擊 OK,然後會彈出另一個對話框,以選擇一個範圍以粘貼所選數據。 看截圖:
文檔粘貼到可見單元格 2

5。 點擊 OK。 您只能看到可見的單元格被粘貼到選定的值。


通過粘貼到Kutools for Excel的可見範圍將值粘貼到可見單元格

實際上,如果您使用 粘貼到可見範圍 的效用 Excel的Kutools –方便的Excel工具,您可以快速輕鬆地解決將值粘貼到可見範圍的問題。
doc粘貼可見單元格6

Excel的Kutools, 與以上 300 方便的功能,使您的工作更加輕鬆。 

安裝後 Kutools for Excel,請執行以下操作:(立即免費下載Kutools for Excel!)

1.選擇要復制和粘貼的值範圍,然後單擊 庫工具 > 範圍 > 粘貼到可見範圍 > 全部 or 僅粘貼值。 看截圖:
doc粘貼可見單元格7

2.然後 粘貼到可見範圍 對話框彈出,然後選擇要粘貼值的範圍。 看截圖:
doc粘貼可見單元格8

3。 點擊 OK。 現在,所選值僅粘貼到可見範圍內。

僅將值粘貼到可見單元格 全部粘貼到可見單元格
doc粘貼可見單元格9 doc粘貼可見單元格10


注意:
此實用程序可用於隱藏和過濾的行或列

單擊此處,了解有關粘貼到可見範圍實用程序的更多詳細信息。

  僅粘貼到可見單元格

最佳辦公生產力工具

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (22)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thank you very much hundred times
This comment was minimized by the moderator on the site
After going absolutely crazy trying to find a solution, this worked - specifically, the macro for pasting INTO visible only cells. Note that I was copying only from unfiltered cells. However, the people complaining that it doesn't do both (which I'm not sure is true) should be quiet because it's pretty easy to copy FROM only visible/filtered cells into an unfiltered area. You don't need a macro for that (and if you're trying to do both, just do the copying of filtered cells in Excel first, then use this macro). Again, what Excel is fully unable to do on its own without a macro is to paste to visible cells only. This macro saved my bum. Thanks!
This comment was minimized by the moderator on the site
Thanks for your recognition.
This comment was minimized by the moderator on the site
This worked perfectly, thanks!
This comment was minimized by the moderator on the site
You can also easily do this without a plugin....
CTRL+C the cells you want to paste
Highlight the filtered/partially hidden range you're pasting to
CTRL+G
Select "Special"
Select "Visible Cells Only" & hit OK
CTRL+V
This comment was minimized by the moderator on the site
Thansk for you reply, but it does not work. With your method, it paste all cells including hidden ones with the copied values.
This comment was minimized by the moderator on the site
VBA method did not work! It copied the invisible cells into the visible cells. Now, I realize that the title doesn't say "How to paste values FROM VISIBLE/FILTERED CELLS to visible/filtered cells only in Excel?" but if my workbook is filtered, it's highly possible that this is what is needed. If this is what you want - copy from one section to another (or to simply paste values from certain cells right back to the same cells, so to remove the fomulas) you first need to copy/paste values to an unfiltered workbook/worksheet. THEN you can use the macro...or at least I hope you can. I didn't save my data and UNDO does not work....
This comment was minimized by the moderator on the site
I am sorry for that.
This comment was minimized by the moderator on the site
So is there any update version for this situation?
This comment was minimized by the moderator on the site
It is even more flexible and functional if the inner loop is constructed with Do While loop.
This comment was minimized by the moderator on the site
This was very helpful. Many thanks.
This comment was minimized by the moderator on the site
i am currently developing a macro. In that i need to do filter for a particular column for Eg: Column D and need to paste some values Eg: Valid in column B. only i need to paste the value for the filtered cells. And also i want to know the coding for multiple filters at a time. EG: filtering in one column and checking the value and again do filter in another column without making false for the previous filter. help me on this.
This comment was minimized by the moderator on the site
This works great up to record 1163 of 23,000 and then it stops. Why would it stop?
This comment was minimized by the moderator on the site
You rock! I've had this issue multiple times in the past but it became a back-breaker for the recent project. And you've helped me to fix it beautifully. THANK YOU!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations