VBA EXCEL 复制范围 [英] VBA EXCEL copy range

问题描述

不久前我发布了同样的问题，并被告知我拼写错误.我把问题记下来了；因为我认为我明白我正在尝试调用一个方法......那是我的范围(是的 sheet 拼写为 sheet).我想我还是不明白.我正在尝试将A2"列通过D"列的最后一个单元格从工作表TEXT"复制到 Sheet3

I posted the same question a little while ago and was told that I had spelled sheets wrong. I took the question down; because I thought that I understood I was trying to call a method..that was my range (and yes sheet was spelled sheeet). I guess I still do not understand. I am trying to copy column "A2" through the last cell of column "D" from the sheet "TEXT" to Sheet3

Dim TotalRows As Integer
Dim CurrentWorkbook As Workbook ' macro workbook
Dim RangeToCopy As Range
Dim ColumnLetterNumber As String

Set CurrentWorkbook = ThisWorkbook
TotalRows = Sheets("TEXT").UsedRange.Rows.Count
ColumnLetterNumber = "D" & TotalRows


Set RangeToCopy = Range("A2", ColumnLetterNumber)

' Group Last Observed within the first three columns
' Last Observed start in column N
' After Insert end up in column )

 Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

' Move the Last observed (now column O) to Column D so that they can be fitered together

Sheets("TEXT").Range(Range("O1"), Range("O1").End(xlDown)).Copy Sheets("TEXT").Range("D1")

With Sheets("TEXT")
 .AutoFilterMode = False
 .Range("A1:D1").AutoFilter
End With
'
'Copy Columns to the POAM sheet
Set RangeToCopy = Range("A2", ColumnLetterNumber) 'top left, bottom right
RangeToCopy.Select
CurrentWorkbook.Sheets("Sheet3").Activate


CurrentWorkbook.Sheets("TEXT").Range(RangeToCopy).Copy Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet3").Range("A1"), unique:=True


End Sub

推荐答案

我已经回答了你之前更详细的问题.这行不通，因为这条线至少有两个原因不起作用:

I answered your more detailed earlier question. This will not work because this line doesn't work for at least 2 reasons:

CurrentWorkbook.Sheets("TEXT").Range(RangeToCopy).Copy Action:=xlFilterCopy, _
    CopyToRange:=Sheets("Sheet3").Range("A1"), unique:=True

首先，RangeToCopy"不是工作表上定义的范围，因此您不能以这种方式引用它.它是 VBA 代码中的一个变量，因此您只需要在此范围内使用 Copy Function.其次，Range 类的 Copy 函数没有所有这些参数.你想要:

First, "RangeToCopy" is not a defined range on the sheet, so you cannot reference it in this way. It is a variable in your VBA code, so you just need to use the Copy Function on this range. Second, the Copy function of the Range Class does not have all of these parameters. You want:

RangeToCopy.Copy Destination:=Sheets("Sheet3").Range("A1")

如果您实际上是在尝试获取唯一值，那么您需要使用AdvancedFilter"功能.

If you are actually tryin get unique values, then you need to use the "AdvancedFilter" function.

RangeToCopy.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet3").Range("A1"), unique:=True,  criteriarange:= <Insert range that you wish to get unique values from here> 

此外，您应该根据工作表名称定义 RangeToCopy，以便:

Also, you should define RangeToCopy based on the Sheet Name so:

Set RangeToCopy = Sheets("TEXT").Range("A2", ColumnLetterNumber)

这篇关于VBA EXCEL 复制范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！