将宏输出拆分为多个工作表

Question

我有一个宏，可以通过数据并按特定格式将其输出到另一个工作表。问题是，在填充了第65,536行后，它会出现错误，我猜这是微软设定的限制：

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets.Add.Name = "Sheet2"

Worksheets("Sheet2").Cells(1, 1).Value = "datacol1"
Worksheets("Sheet2").Cells(1, 2).Value = "datacol2"
Worksheets("Sheet2").Cells(1, 3).Value = "datacol3"

Worksheets("data").Activate
SourceColumn = 2
SourceRow = 2
Cells(SourceRow, 1).Activate
targetRow = SourceRow
targetcolumn = 1

While Cells(1, SourceColumn).Value <> ""

While ActiveCell.Value <> ""
Worksheets("Sheet2").Cells(targetRow, targetcolumn).Value = ActiveCell.Value
Worksheets("Sheet2").Cells(targetRow, targetcolumn + 1).Value =          Worksheets("Data").Cells(1, SourceColumn).Value
Worksheets("Sheet2").Cells(targetRow, targetcolumn + 2).Value =   Worksheets("Data").Cells(SourceRow, SourceColumn).Value
SourceRow = SourceRow + 1
targetcolumn = 1
targetRow = targetRow + 1
Cells(SourceRow, 1).Activate
Wend
SourceColumn = SourceColumn + 1
SourceRow = 2
Cells(SourceRow, 1).Activate

Wend
With Worksheets("Sheet2").Sort
.SetRange Range(Cells(2, 1), Cells(targetRow, 3))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub  

我该在这个宏中添加什么，以便当它达到65,536的限制时，创建一个新的工作表并继续执行？

- user5495762

---

你正在使用Excel 2003或更早的版本？ - Hambone

我正在使用Excel 2010。 - user5495762

Answer 1

我认为这不是Excel的限制。我认为问题在于您的变量未定义，因此VBA正在尽最大努力使用一个数据类型来处理您提供的行号，但该数据类型不足以处理。通过将数据类型更改为 long，我认为您将解决您的问题。

作为演示，这应该解决两个问题。出于说明目的，我使工作表在100,000行处分割（而不是65k，以显示它能够工作），但您可以将其更改为1,000,000或实际限制。我将其设置为100,000只是为了证明它确实可以分割。

Sub Macro1()
  Dim wsFrom, wsTo As Worksheet
  Dim SourceRow, SheetNumber, TargetRow As Long
  Dim val As String

  Set wsFrom = Worksheets("data")

  SheetNumber = 2
  SourceRow = 2

  val = wsFrom.Cells(SourceRow, 1).Text

  While val <> ""

    Sheets.Add.Name = "Sheet" & SheetNumber
    Set wsTo = Worksheets("Sheet" & SheetNumber)

    wsTo.Cells(1, 1).Value = "datacol1"
    wsTo.Cells(1, 2).Value = "datacol2"
    wsTo.Cells(1, 3).Value = "datacol3"
    TargetRow = 2

    While val <> "" And TargetRow < 100000
      wsTo.Cells(TargetRow, 1).Value = wsFrom.Cells(SourceRow, 1).Value
      wsTo.Cells(TargetRow, 2).Value = wsFrom.Cells(SourceRow, 2).Value
      wsTo.Cells(TargetRow, 3).Value = wsFrom.Cells(SourceRow, 3).Value

      SourceRow = SourceRow + 1
      TargetRow = TargetRow + 1
      val = wsFrom.Cells(SourceRow, 1).Text

    Wend
    ' insert your sort logic here
    SheetNumber = SheetNumber + 1
  Wend
End Sub

- Hambone

---

谢谢您的快速回复，但是这似乎产生了一些奇怪的结果。它将列1的值输出到列2，而不是所有内容都被导出到新工作表中。 如果我可以在一个工作表中完成所有操作，那就太完美了，如果可以的话，我绝对不想创建多个工作表。 供参考，我已经创建了一个示例工作表，请查看链接： https://app.box.com/s/4uq8j828bu6u1hvt7aggamb0eiomno6t - user5495762

是的，它就是将数据从一个表格复制到另一个表格。 - user5495762

抱歉，为了更明确，它需要将工作表1的列1中的所有值粘贴到工作表2的列1中，然后将工作表1的列2标题复制到所有列1条目的列2中，然后将工作表1的列2值复制到工作表2的列3中。完成后，它需要重新粘贴工作表1的列1值，复制工作表1的列3标题，将其粘贴到工作表2的列2中（上一个结束的位置），以此类推。希望这样更清楚一些。 - user5495762

你有没有可能复制一下sheet1和sheet2的快照，让我们看一下它们变化前后的样子？我大概明白你的意思，但是你知道什么吗，“一图胜千言”…如果你可以在原帖中编辑这些图片，我相信我们应该能够解决这个问题。 - Hambone

很有趣。我猜我的原始问题（使用Excel 2003）并没有太离谱。很高兴你解决了它。 - Hambone