如何将整列复制到从某行开始的另一列 [英] how to copy the whole column to another column starting from a certain row

问题描述

我正在做一个宏，将整个列从一个工作表复制到另一个工作表中从第2行开始的另一个列。但是我不认为它是正常工作， Set des = Sheets（3）.Range（P2：P& Rows（i））这是行原因错误

I'm currently doing a macro to copy the whole column from one worksheet to another column starting from row 2 in another worksheet. However I don't think it's working, Set des = Sheets(3).Range("P2:P" & Rows(i)) this is the line causes error.

Sub Sample()
Dim lastRow As Long, i As Long
Dim CopyRange As Range
Dim rw As Range
Dim rw1 As Range
Dim rw2 As Range
Dim rw3 As Range
Dim des As Range
Dim des1 As Range
Dim des2 As Range
Dim des3 As Range
'~~> Change Sheet1 to relevant sheet name
With Sheets(1)
    lastRow = .Range("A" & .Rows.Count).End(xlUp).row

    For i = 2 To lastRow
        If Len(Trim(.Range("A" & i).Value)) <> 0 Then
            If CopyRange Is Nothing Then
                Set CopyRange = .Rows(i)
            Else
                Set CopyRange = Union(CopyRange, .Rows(i))
                Set rw = .Columns(16)
                Set rw1 = .Columns(23)
                Set rw2 = .Columns(3)
                Set rw3 = .Columns(18)
            End If
        End If
    Next

    If Not CopyRange Is Nothing Then
        Set des = Sheets(3).Range("P2:P" & Rows(i))
        Set des1 = Sheets(3).Columns("R")
        Set des2 = Sheets(3).Columns("T")
        Set des3 = Sheets(3).Columns("U")
        '~~> Change Sheet2 to relevant sheet name
        rw.Copy des
        rw1.Copy des1
        rw2.Copy des2
        rw3.Copy des3

        Application.CutCopyMode = False
    End If
End With
End Sub

推荐答案

您需要将复制的列更改为范围，以便将其正确粘贴：

You need to change the copied columns to ranges to paste them properly like this:

Sheets(1).Range("A1:A" & Sheets(1).Cells(Sheets(1).Rows.Count, "a").End(xlUp).Row).Copy ' this code copies from first to last cell with data in column A

然后，您需要使用例如

sheets(3).range("R2").paste

这篇关于如何将整列复制到从某行开始的另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！