嗨,我有一个代码,它根据给定的条件复制列并粘贴到另一张表中。我想更改粘贴位置,它应该从"C5"
WF-4 CC with Values
工作表开始粘贴,但我无法这样做。它给出了选择方法失败的错误。我需要一些关于如何更改粘贴范围位置的帮助。谢谢:)这是代码:
Sub ExtractCCData4()
Dim lastcol As Long
Dim j As Long
With Worksheets("WF - L4")
lastcol = .Cells(5, Columns.Count).End(xlToLeft).Column
For j = 3 To lastcol
If Application.WorksheetFunction.Sum(.Columns(j)) > 0 Then
.Columns(j).Copy Destination:=Worksheets("WF-4 CC with Values").Columns(j)
Else
End If
Next
End With
End Sub
在OP澄清后编辑
我假设
-
"它应该从"C5"开始粘贴意味着它从"C5"向右无缝粘贴,而不考虑跳过的"WF-L4"列
-
您只需要粘贴值
那就试试这个
Option Explicit
Sub ExtractCCData4()
Dim lastcol As Long
Dim j As Long, jPaste As Long
Dim rngToCopy As Range
With Worksheets("WF - L4")
lastcol = .Cells(5, .Columns.Count).End(xlToLeft).Column
For j = 3 To lastcol
If Application.WorksheetFunction.Sum(.Columns(j)) > 0 Then
Set rngToCopy = .Range(.Cells(1, j), .Cells(.Rows.Count, j).End(xlUp))
Worksheets("WF-4 CC with Values").Range("C5").Offset(, jPaste).Resize(rngToCopy.Rows.Count).Value = rngToCopy.Value
jPaste = jPaste + 1 '<~~ updating column offsetting from "WF - L4" sheet column "C"
Else
End If
Next
End With
End Sub