vba - VBA不断更新范围的列号

Question

我想从每个工作表中复制数据，然后将其复制到新的工作表中. 如何在迭代时不断更改行号？

Sub Macro1()

Dim ws As Worksheets 
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 
ws_num = ThisWorkbook.Worksheets.Count - 2

For I = 1 To ws_num
    e = 9
    s = 39
    ThisWorkbook.Worksheets(I).Activate
    Range("A9:J39").Select
    Selection.Copy
    rr = "A" + CStr(s) + ":" + "J" + CStr(e)
    Worksheets("scrap").Range(rr).Paste
    e = e + 30
    s = s + 30
   Next
End Sub

标签： vbaexcel

Answer 1

VBA 中的字符串连接运算符是&，不是+。因此，尝试：

rr = "A" & CStr(s) & ":" & "J" & CStr(e)

这里有两点可以进一步改进：

• 使用 VBA 时避免选择和激活： 如何避免在 Excel VBA 中使用选择；

• 写Option Explicit在模块的顶部并确保显式声明每个变量。-选项显式 MSDN

---

一般来说，这应该做需要的：

Sub TestMe()

    Dim counter As Long
    Dim wsTotal As Long

    wsTotal = ThisWorkbook.Worksheets.Count - 2

    Dim starting As Long: starting = 9
    Dim ending As Long: ending = 39

    Dim ws As Worksheet
    Dim rangeToCopy As Range

    For counter = 1 To wsTotal
        Set ws = ThisWorkbook.Worksheets(counter)
        With ws
            Set rangeToCopy = .Range(.Cells(starting, "A"), .Cells(ending, "J"))
            rangeToCopy.Copy ThisWorkbook.Worksheets("scrap").Range(rangeToCopy.Address)
        End With

        starting = starting + 30
        ending = ending + 30
    Next counter

End Sub