VBA 宏根据工作表中的行数将一个工作表拆分为多个工作簿

VBA macro to split one worksheet into several workbooks based on number of rows in the worksheet

提问by Parseltongue

I am sanitizing contact data in an excel spreadsheet in order to bulk upload into SalesForce. SalesForce, however, can only manage worksheets 100 contacts long. I want a macro that can split up a 700 row worksheet, for example, into seven workbooks, each containing 100 cell references.

我正在清理 Excel 电子表格中的联系人数据，以便批量上传到 SalesForce。但是，SalesForce 只能管理长度为 100 个联系人的工作表。例如，我想要一个可以将 700 行工作表拆分为七个工作簿的宏，每个工作簿包含 100 个单元格引用。

I did some research on how to go about doing this, and I believe this is the only reference on StackOverflow to aid in this end: Solution for Dividing WorkSheet into Multiple Files with vba/excel/c#

我做了一些关于如何去做这件事的研究，我相信这是 StackOverflow 上唯一有助于实现这一目标的参考： Solution for Dividing WorkSheet into Multiple Files with vba/excel/c#

Also, this solution looks promising, but I don't understand enough VBA to grasp it just yet: https://superuser.com/questions/57157/can-i-split-a-spreadsheet-into-multiple-files-based-on-a-column-in-excel-2007

此外，这个解决方案看起来很有希望，但我还不了解足够的 VBA 来掌握它：https: //superuser.com/questions/57157/can-i-split-a-spreadsheet-into-multiple-files-based -on-a-column-in-excel-2007

However, the selected answer doesn't really serve my ends. Can someone point me in the right direction / enumerate the commands for doing this?

但是，所选答案并不能真正满足我的目的。有人可以指出我正确的方向/枚举执行此操作的命令吗？

Here's what I have so far-- this appears to produce the right number of workbooks. Now I just need to figure out how to cut and paste 100 rows into each one.

这是我到目前为止所拥有的 - 这似乎产生了正确数量的工作簿。现在我只需要弄清楚如何将 100 行剪切并粘贴到每一行中。

    Sub Macro12()

    Dim wb As Workbook
    Dim p As Double
    Dim p_quotient As Double
    Application.ScreenUpdating = False

    p = ActiveSheet.UsedRange.Rows.Count
    p_quotient = p / 100
    p_quotient = Application.WorksheetFunction.RoundUp(p_quotient, 0)

    For i = 1 To p_quotient
        Workbooks.Add
        Set wb = ActiveWorkbook
        ThisWorkbook.Activate
        wb.SaveAs ThisWorkbook.Path & "test" & i
        wb.Close

    Next i
    Application.ScreenUpdating = True
    Set wb = Nothing


End Sub

Here's the code I'm using now:

这是我现在使用的代码：

Sub Macro12()

  Dim wb As Workbook
      Dim ThisSheet As Worksheet
      Dim NumOfColumns As Integer
      Dim RangeToCopy As Range
      Dim WorkbookCounter As Integer
      Dim myDate As String
      myDate = Format(Date, "yyyy.mm.dd")

      Set ThisSheet = ThisWorkbook.ActiveSheet
      NumOfColumns = ThisSheet.UsedRange.Columns.Count
      WorkbookCounter = 1


      For p = 1 To ThisSheet.UsedRange.Rows.Count Step 101
        Set wb = Workbooks.Add

        Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + 100, NumOfColumns))
        RangeToCopy.Copy wb.Sheets(1).Range("A1")

        wb.SaveAs ThisWorkbook.Path & "\Salesforce Lead Conversion " & myDate & " Part " & WorkbookCounter & ".xls"
        wb.Close
        WorkbookCounter = WorkbookCounter + 1
      Next p

      Application.ScreenUpdating = True
      Set wb = Nothing

  End Sub

回答by John Bustos

Following your logic, this should do it:

按照你的逻辑，这应该这样做：

  Sub Macro12()

      Dim wb As Workbook
      Dim ThisSheet As Worksheet
      Dim NumOfColumns As Integer
      Dim RangeToCopy As Range
      Dim WorkbookCounter As Integer

      Application.ScreenUpdating = False


      Set ThisSheet = ThisWorkbook.ActiveSheet
      NumOfColumns = ThisSheet.UsedRange.Columns.Count
      WorkbookCounter = 1

      For p = 1 To ThisSheet.UsedRange.Rows.Count Step 101
        Set wb = Workbooks.Add

        Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + 100, NumOfColumns))
        RangeToCopy.Copy wb.Sheets(1).Range("A1")

        wb.SaveAs ThisWorkbook.Path & "\test" & WorkbookCounter
        wb.Close
        WorkbookCounter = WorkbookCounter + 1
      Next p

      Application.ScreenUpdating = True
      Set wb = Nothing


  End Sub

回答by John Bustos

Although there are MANY ways to do this (some even extremely easy with VBA) What I think would be the easiest that requires no VBA would be to do the following:

尽管有很多方法可以做到这一点（有些甚至非常容易使用 VBA）我认为最简单的不需要 VBA 的方法是执行以下操作：

• Add in an extra "helper" column
• In that column, put in the following formula =FLOOR(ROW()/100,1)
• Do a filter on the data

• 添加一个额外的“助手”列
• 在该列中，输入以下公式 =FLOOR(ROW()/100,1)
• 对数据进行过滤

Now, filter for each number 0,1,2,3,...,7 in your helper column.

现在，过滤帮助列中的每个数字 0,1,2,3,...,7。

Each set will have your next hundred records - Copy and paste them into a new workbook and save it as needed. Also, from this point it's TRULY a trivial case to record a quick macro to do that last part.

每组将包含您接下来的 100 条记录 - 将它们复制并粘贴到新工作簿中，并根据需要进行保存。此外，从这一点来看，录制一个快速宏来完成最后一部分确实是一个微不足道的案例。

Even though it isn't exactly what you asked for, I hope the simplicity of it helps.

即使它不完全是您所要求的，我希望它的简单性有所帮助。