如何使用VBA复制特定的列 [英] How to copy specific columns using VBA
问题描述
有没有办法我可以更改以下代码以仅复制特定的单元格范围或列:
Is there a way I can change the following code to only copy specific cells range or columsn:
例如:我在A到Z的所有列中都有数据.我想将数据复制到另一张纸上,但是我只想复制A,D,H和J列中的数据(A2,D2,H2,J2).
For example: I have data in all columns from A to Z. I want to copy data to another sheet but I only want to copy the data from Column A, D, H and J(A2, D2, H2, J2).
Option Explicit
Private Sub Worksheet_Activate()
Dim LR As Long
Me.UsedRange.Offset(1).ClearContents 'clear existing data
With Sheets("Raw - Incident Request Report")
.AutoFilterMode = False 'remove any prior filtering
.Rows(1).AutoFilter 'activate autofilter
.Rows(1).AutoFilter 27, Criteria1:="Breached" 'filter column D for 80%+
LR = .Range("D" & .Rows.Count).End(xlUp).Row 'is any data visible?
If LR > 1 Then
.Range("AC7:AC" & LR).Copy Range("C3") 'copy any data visible to report
.Range("D7:D" & LR).Copy Range("D3")
.Range("I7:I" & LR).Copy Range("E3")
.Range("K7:K" & LR).Copy Range("F3")
.Range("T7:T" & LR).Copy Range("G3")
Else
Range("C3") = "No Data Found" 'if none, give that message
End If
.AutoFilterMode = False 'turn off autofilter
End With
End Sub
最终代码-
FINAL CODE - EDITED:
Option Explicit
Private Sub Worksheet_Activate()
Dim LR As Long
Me.UsedRange.Offset(17).ClearContents
With Sheets("Raw - Incident Request Report")
.AutoFilterMode = False
LR = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("D6:AH" & LR).AutoFilter Field:=26, Criteria1:="<>"
If LR > 1 Then
.Range("AC7:AC" & LR).Copy
Sheets("Tickets").Range("C17").PasteSpecial xlPasteValues
.Range("D7:D" & LR).Copy
Sheets("Tickets").Range("D17").PasteSpecial xlPasteValues
.Range("I7:I" & LR).Copy
Sheets("Tickets").Range("E17").PasteSpecial xlPasteValues
.Range("K7:K" & LR).Copy
Sheets("Tickets").Range("F17").PasteSpecial xlPasteValues
.Range("T7:T" & LR).Copy
Sheets("Tickets").Range("G17").PasteSpecial xlPasteValues
Else
Range("C17") = "No Data Found"
End If
.AutoFilterMode = False
End With
End Sub
推荐答案
未经测试,但尝试更改
.Range("A2:F" & LR).Copy Range("A2")
到
.Range("H2:H" & LR).Copy Range("A2") 'copy any data visible to report
.Range("D2:D" & LR).Copy Range("B2")
.Range("J2:J" & LR).Copy Range("C2")
.Range("A2:A" & LR).Copy Range("D2")
当过滤器标题位于第6行时,您尝试在第1行进行过滤.您还应尝试设置确切的范围,以在其上而不是整个行上应用自动过滤器.
You are trying to filter on Row 1 when your filter headers are on row 6. You should also try to set the exact range to apply an autofilter on as well rather than the entire row.
.AutoFilterMode = False
.Range("D6:AF6").AutoFilter Field:=24, Criteria1:="Breached"
此外,您的PasteSpecial无效,因为语法不正确.您必须先复制,然后再复制某个范围内的PasteSpecial.
Also, your PasteSpecial isn't working because the syntax isn't correct. You have to Copy first, then PasteSpecial on a range.
http://msdn.microsoft.com/en-us/library/office/ff839476.aspx
这篇关于如何使用VBA复制特定的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!