Excel VBA咨询：Form提交后按指定次数批量复制数据到OSP工作表

基础功能实现（按输入固定数值批量复制行） #

首先确认Form表中「Number of Strands」对应的单元格位置，假设该值存放在Range("C20")（可根据你的实际布局修改单元格地址），修改后的代码如下：

Sub data_input()
    Dim ws_output As String
    Dim next_row As Long
    Dim strand_count As Long
    Dim i As Long
    ' 定义输出工作表名
    ws_output = "OSP"
    ' 读取输入的股数，注意修改后面的单元格地址为你实际的Number of Strands位置
    strand_count = Val(Sheets("Form").Range("C20").Value)
    ' 股数合法性校验
    If strand_count < 1 Then
        MsgBox "请输入有效的股数，值需大于等于1", vbExclamation
        Exit Sub
    End If
    ' 获取OSP表的首个空行位置
    next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    ' 循环写入对应行数的表单数据
    For i = 1 To strand_count
        ' 填充Form表C列对应的字段
        Sheets(ws_output).Cells(next_row + i - 1, 1).Value = Sheets("Form").Range("C6").Value
        Sheets(ws_output).Cells(next_row + i - 1, 2).Value = Sheets("Form").Range("C8").Value
        Sheets(ws_output).Cells(next_row + i - 1, 3).Value = Sheets("Form").Range("C10").Value
        Sheets(ws_output).Cells(next_row + i - 1, 4).Value = Sheets("Form").Range("C12").Value
        Sheets(ws_output).Cells(next_row + i - 1, 5).Value = Sheets("Form").Range("C14").Value
        Sheets(ws_output).Cells(next_row + i - 1, 6).Value = Sheets("Form").Range("C16").Value
        Sheets(ws_output).Cells(next_row + i - 1, 7).Value = Sheets("Form").Range("C18").Value
        ' 填充Form表G列对应的字段
        Sheets(ws_output).Cells(next_row + i - 1, 8).Value = Sheets("Form").Range("G6").Value
        Sheets(ws_output).Cells(next_row + i - 1, 9).Value = Sheets("Form").Range("G8").Value
        Sheets(ws_output).Cells(next_row + i - 1, 10).Value = Sheets("Form").Range("G10").Value
        Sheets(ws_output).Cells(next_row + i - 1, 11).Value = Sheets("Form").Range("G12").Value
        Sheets(ws_output).Cells(next_row + i - 1, 12).Value = Sheets("Form").Range("G14").Value
        Sheets(ws_output).Cells(next_row + i - 1, 13).Value = Sheets("Form").Range("G16").Value
        Sheets(ws_output).Cells(next_row + i - 1, 14).Value = Sheets("Form").Range("G18").Value
    Next i
    ' 可选功能：提交后清空表单内容，按需取消注释即可使用
    ' Sheets("Form").Range("C6,C8,C10,C12,C14,C16,C18,G6,G8,G10,G12,G14,G16,G18,C20").ClearContents
    MsgBox "提交成功，共新增" & strand_count & "行记录", vbInformation
End Sub

注意说明 #

代码中强制指定取值来源为Form表，避免了当前活动表不是Form时取值错误的问题。

进阶功能实现（支持范围输入+自动填充Strand Number） #

你提到的进阶功能实现逻辑非常简单，仅需增加字符串拆分逻辑即可实现。假设OSP表的「Strand Number」列是第15列（即O列，可根据实际布局修改列号），完整代码如下：

Sub data_input_advanced()
    Dim ws_output As String
    Dim next_row As Long
    Dim strand_input As String
    Dim start_num As Long, end_num As Long
    Dim i As Long, strand_count As Long
    ws_output = "OSP"
    ' 读取Number of Strands输入值，修改后面的单元格地址为实际位置
    strand_input = Trim(Sheets("Form").Range("C20").Value)
    ' 解析输入内容
    If InStr(strand_input, "-") > 0 Then
        ' 范围输入格式
        start_num = Val(Split(strand_input, "-")(0))
        end_num = Val(Split(strand_input, "-")(1))
        If start_num > end_num Then
            MsgBox "范围输入错误，起始值不能大于结束值", vbExclamation
            Exit Sub
        End If
        strand_count = end_num - start_num + 1
    Else
        ' 单数值输入格式
        start_num = 1
        end_num = Val(strand_input)
        strand_count = end_num
    End If
    ' 合法性校验
    If strand_count < 1 Then
        MsgBox "请输入有效的股数/股数范围", vbExclamation
        Exit Sub
    End If
    next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    ' 循环写入数据
    For i = start_num To end_num
        ' 填充通用表单字段
        Sheets(ws_output).Cells(next_row + i - start_num, 1).Value = Sheets("Form").Range("C6").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 2).Value = Sheets("Form").Range("C8").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 3).Value = Sheets("Form").Range("C10").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 4).Value = Sheets("Form").Range("C12").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 5).Value = Sheets("Form").Range("C14").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 6).Value = Sheets("Form").Range("C16").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 7).Value = Sheets("Form").Range("C18").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 8).Value = Sheets("Form").Range("G6").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 9).Value = Sheets("Form").Range("G8").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 10).Value = Sheets("Form").Range("G10").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 11).Value = Sheets("Form").Range("G12").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 12).Value = Sheets("Form").Range("G14").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 13).Value = Sheets("Form").Range("G16").Value
        Sheets(ws_output).Cells(next_row + i - start_num, 14).Value = Sheets("Form").Range("G18").Value
        ' 填充Strand Number，末尾的15改为你实际的列号即可
        Sheets(ws_output).Cells(next_row + i - start_num, 15).Value = i
    Next i
    MsgBox "提交成功，共新增" & strand_count & "行记录", vbInformation
End Sub

内容的提问来源于stack exchange，提问作者Mike Rider