将表复制到另一个工作表 [英] Copy tables to another worksheet

问题描述

我在Worksheet(1)中有2个表"Table1"和"Table2".

I have 2 tables, "Table1" and "Table2" in Worksheet(1).

我想将这些表复制到另一个工作表(2)，其中Table1的左上角在新工作表中的单元格"A1"处，而Table2的左上角在新工作表中的单元格"O1"处."

I would like to copy these tables to another worksheet(2) where the top left corner of Table1 is in the new worksheet at cell "A1" and the Top left corner cell of Table2 is in the new worksheet at cell "O1."

除非在Sheet2中ActiveCell为A1，否则表将无法正确复制和粘贴.我不能正确激活工作表吗?

Unless the ActiveCell is A1 in Sheet2, the tables won't copy and paste correctly. Am I not activating a sheet correctly?

Sub CopyTables()
Worksheets(2).Activate          'must be on ExHouStorm or Worksheet(1)
Worksheets(1).ListObjects("Table1").Range.Copy
Worksheets(2).Paste


Worksheets(1).ListObjects("Table2").Range.Copy
Worksheets(2).Range("O1").Select
Worksheets(2).Paste

End Sub

这看起来很简单，因为它可以部分工作，但是我认为我在语法上有误.

This seems pretty simple as it partially works but I think I have a syntax mistake.

推荐答案

您应该可以通过提供目标位置直接进行复制.

You should be able to copy directly by providing the destination.

Sub CopyTables()

    Worksheets(1).ListObjects("Table1").Range.Copy _
        Destination:=Worksheets(2).Range("A1")


    Worksheets(1).ListObjects("Table2").Range.Copy _
        Destination:=Worksheets(2).Range("O1")

End Sub

通过直接显式地寻址对象及其目的地，可以完全避免使用.Activate和.Select.

By directly and explicitly addressing the objects and their destinations, you can avoid .Activate and .Select altogether.

请参见如何避免在Excel VBA宏中使用选择" 了解更多摆脱依赖选择和激活来实现目标的方法.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

这篇关于将表复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！