excel - 粘贴为值
问题描述
我真的需要帮助将以下数据集粘贴为值。
我目前想遍历我的工作簿中的某些工作表,并从选定的工作表中复制和粘贴数据。
但是,我不确定如何将我正在处理的数据粘贴为值。
我编写了以下代码来遍历我想要的工作表,并从每个工作表中选择我想要的日期。但是,当我将此数据粘贴到主数据选项卡中时,我似乎无法粘贴为值。
Sub exa()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If Not wks.Name = "Master Data" _
And Not wks.Name = "Query --->" _
And Not wks.Name = "Pivot Portfolio Movement" _
And Not wks.Name = "PortfolioMovement - All" _
And Not wks.Name = "Bank Holidays" _
And Not wks.Name = "Property" _
And Not wks.Name = "Postcodes" _
And Not wks.Name = "Product" _
And Not wks.Name = "PartRedemption" _
And Not wks.Name = "Wrap" _
And Not wks.Name = "Completions Database" _
And Not wks.Name = "Default" _
And Not wks.Name = "ReturningBorrower" _
And Not wks.Name = "Extensions" _
And Not wks.Name = "PortfolioMovement" _
And Not wks.Name = "Drawdowns" _
And Not wks.Name = "Dev Interest WIP" _
And Not wks.Name = "Write Off Loans" _
And Not wks.Name = "Interest Rate" _
And Not wks.Name = "Admin" _
And Not wks.Name = "Datatape --->" _
And Not wks.Name = "Data" _
And Not wks.Name = "Drawn Balance by Loan" _
And Not wks.Name = "Sheet1" Then
wks.Range("H2:N2" & wks.Cells(Rows.Count, "N").End(xlUp).Row).Copy _
Destination:=Worksheets("Master Data").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
Next
End Sub
解决方案
要粘贴为值,您需要使用 Range.PasteSpecial 方法:.PasteSpecial xlPasteValues
请注意,如果您使用数组来跳过工作表,则可以像这样减少代码:
Option Explicit
Public Sub exa()
Dim SkipWorksheets As Variant
SkipWorksheets = Array("Master Data", "Query --->", "Pivot Portfolio Movement", "PortfolioMovement - All", _
"Bank Holidays", "Property", "Postcodes", "Product", "PartRedemption", "Wrap", _
"Completions Database", "Default", "ReturningBorrower", "Extensions", _
"PortfolioMovement", "Drawdowns", "Dev Interest WIP", "Write Off Loans", _
"Interest Rate", "Admin", "Datatape --->", "Data", "Drawn Balance by Loan", "Sheet1")
Dim Wks As Worksheet
For Each Wks In ThisWorkbook.Worksheets
If Not (UBound(Filter(SkipWorksheets, Wks.Name)) > -1) Then
Wks.Range("H2:N2" & Wks.Cells(Rows.Count, "N").End(xlUp).Row).Copy
Worksheets("Master Data").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next Wks
End Sub
推荐阅读
- python - 字符串中的 Python 字符匹配
- python - 如何根据日期列总结python中tablewidget的所有价格?
- sql - 如何使用 ViewModel 构建控制器
- delphi - 通过Delphi带参数执行tregsvr.exe
- r - 循环时使用 group_by 汇总数据
- python - 用于 Windows 的带有 git 安装的 Docker 文件
- reactjs - 当路由来自另一个组件时,React路由器重定向不起作用
- awk - 如何使用awk为列打印过滤条件下的所有列?
- rabbitmq - 当 RabbitMQ 交换不存在时如何处理错误(并且消息是通过消息传递网关接口发送的)
- javascript - 如何在 JavaScript 中获取 td id