使用VBA将数据从一张纸移动到另一张纸 [英] Move data from one sheet to another with VBA

问题描述

我有一个包含4张纸的Excel工作簿.

I have an Excel workbook with 4 sheets.

1. 主表
2. test_1
3. test_2
4. test_3

我想将数据从主表"移动到所有其他表，这是通过创建宏完成的.每天，主工作表数据将不断增加，因此如何在宏中容纳此更改.

I want to move the data from Master Sheet to all the other sheets, which I have done by creating a macro. Daily the Master sheet data will be increasing, so how can I accommodate this change in the Macro.

我在下面粘贴了现有代码:

I have pasted my existing code below:

Sub sbCopyRangeToAnotherSheet()
    Sheets("Master").Range("B10:M1628").Copy
    Sheets("test_1").Activate
    Range("B9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = Flase
End Sub


Sub sbCopyRangeToCRP2()
    Sheets("Master").Range("B10:M1628").Copy
    Sheets("test_2").Activate
    Range("B9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = Flase
End Sub


Sub sbCopyRangeToCRP3()
    Sheets("Master").Range("B10:M1628").Copy
    Sheets("test_3").Activate
    Range("B9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = Flase
End Sub

在上面的代码中，我提到了Master Sheet的硬编码范围值，该值从B10开始，在M1628结束.

In the above code I mentioned the hard coded range value of Master Sheet which starts from B10 and ends at M1628.

行数增加**(B10范围将保持不变)**，我不想对范围进行硬编码.我该怎么做?

Going forward number of rows increase**(B10 range will remain)** and I don't want to hard code the Range. How can I accomplish this?

推荐答案

我建议通过将工作表作为参数，将这3个子项合并为一个可以重复使用的子项.

I suggest to combine these 3 subs to one that you can re-use by giving the worksheet as parameter:

Sub sbCopyRangeToAnotherSheet(ToSheet As Worksheet)
    Dim LastUsedRow As Long

    With Sheets("Master")
        LastUsedRow = .UsedRange.Row + .UsedRange.Rows.Count - 1
        .Range("B10:M" & LastUsedRow).Copy ToSheet.Range("B9")
    End With

    Application.CutCopyMode = False
End Sub

然后，您可以为任何工作表名称运行此子项，例如

Then you can run this sub for any sheet name like

Sub test_1()
    sbCopyRangeToAnotherSheet Sheets("test_1")
    'and for the second sheet
    sbCopyRangeToAnotherSheet Sheets("test_2")
End Sub

这篇关于使用VBA将数据从一张纸移动到另一张纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！