excel - 如何使用vba在excel中仅复制和粘贴过滤的单元格
问题描述
我一直在尝试使用 vba 将一系列过滤后的单元格复制并粘贴到我的 Excel 工作表中的特定空间(查看图像),但是当我尝试这样做时,
错误 1004
发生。我在很多论坛中搜索并尝试以不同的方式解决我的问题,但它不起作用并且仍然出现错误 1004。
Sub arrumando_dados_pro_xml()
Dim n As Integer
Dim i As Integer
Dim m As Integer
Dim j As Integer
n = Cells(1000, 1).End(xlUp).Row
j = Cells(n - 1, 1).End(xlUp).Row
m = Cells(1, 50).End(xlLeft).Row
Range(Worksheets("Planilha1").Cells(2, 1), Worksheets("Planilha1").Cells(j, m)).SpecialCells(xlCellTypeVisible).Copy
'''Range("A2:P37").SpecialCells(xlCellTypeVisible).Select
''''Selection.SpecialCells(xlCellTypeVisible).Select
'''Selection.SpecialCells(xlCellTypeVisible).Copy
''''Call Plan1.AutoFilter.Range.Copy
Range(Worksheets("Planilha2").Cells(1, 1), Worksheets("Planilha2").Cells(1, m)).Paste
Range(Worksheets("Planilha2").Cells(1, 1), Worksheets("Planilha2").Cells(1, m)).Copy
Range(Worksheets("Planilha1").Cells(n, 1), Worksheets("Planilha2").Cells(n, m)).Copy
''' Range(Cells(n, 1), Cells(n, m)).Select
''' ActiveSheet.Paste
End Sub
解决方案
由于您的代码有点混乱,因此我对其进行了简化。这是一个带有注释的基本代码示例,用于复制范围内的可见单元格并粘贴。它可以根据需要进行修改。
'Declare your variables
Dim ws1 As Worksheet, ws2 As Worksheet, As Range, lRow As Long, lCol As Long
'Assign your variables, you should always identify the workbook and worksheet
'ThisWorkbook refers to the workbook where your code resides
Set ws1 = ThisWorkbook.Sheets("Planilha1")
Set ws2 = ThisWorkbook.Sheets("Planilha2")
'Using your worksheet variable find the last used row and last used column
lRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
lCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
'Define your range by resizing using lRow and lCol.
Set rng = ws1.Cells(2, 1).Resize(lRow - 1, lCol)
'Copy the visible cells in the range(normally used after filtering or with hidden rows/columns)
rng.SpecialCells(xlCellTypeVisible).Copy
'paste the copied range starting on row 1, after the last column with data, by using .Offset(, 1)
ws2.Cells(1, 1).PasteSpecial xlPasteValues
如果您有任何问题,请提出,我会提供帮助。
已编辑我修改了您的代码,必须进行更改,请参阅评论
'Added worksheet variables
Dim ws1 As Worksheet, ws2 As Worksheet, n As Long, m As Long 'removed j As Long
Set ws1 = ThisWorkbook.Sheets("Planilha1")
Set ws2 = ThisWorkbook.Sheets("Planilha2")
n = ws1.Cells(1000, 1).End(xlUp).Row
'Removed [j = ws1.Cells(n - 1, 1).End(xlUp).Row] if there are no blank cells after "n" the new last used row then j = 1
m = ws1.Cells(1, 50).End(xlToLeft).Column 'you can't use .End(xlLeft).Row to get the last column
'changed j to n, if j = 1 then only the top two rows will be copied
ws1.Range(ws1.Cells(2, 1), ws1.Cells(n, m)).SpecialCells(xlCellTypeVisible).Copy
'when pasting, just use one cell
ws2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False 'Exits the CutCopyMode, removes "Marching Ants"
推荐阅读
- php - 根据订购的产品动态覆盖 WooCommerce Stripe 键
- c++ - 在 std::tuple 上使用 std::variant 的问题
- sql - 为什么 null||null 在 postgres 中返回一个空值而 concat(null,null) 返回一个空字符串?
- ignite - 在 Apache Ignite 中合并两个持久缓存
- python-2.7 - 当我对语音识别说话时没有响应
- tensorflow - ImportError: import tensorflow_model_analysis as tfma [PYTHON on datalab]
- r - 在多个(唯一和二进制)列中拆分调查文本单元格
- javascript - 来自动态 JS 的图像源相对路径未正确加载
- amazon-ecs - AWS Fargate vs Batch vs ECS 每天一次的批处理过程
- android - Android Studio 无法识别 wav 文件