excel - 根据用户输入生成行数

Question

我正在尝试使用 2 个工作表生成装箱单。第二个工作表将获取所有数据并将其引用到第一个工作表中。

第 2 个工作表中有一个项目部分，会将用户输入的整数值作为 Totalpieces 以在第 1 个工作表中生成重复的项目记录行。一旦 items 部分完成 for 循环，它应该继续循环通过外部 for 循环，以便下一部分参考第一个工作表。

我试图做一个 for 循环将所有数据从第二个工作表复制到第一个工作表。以及此项目部分的嵌套 for 循环，但我遇到宏无限循环并使工作簿崩溃。希望就如何最好地做到这一点征求您的意见。

下面是第一个工作表的代码。

Dim No_Of_Shpt As Integer
Dim counter As Integer
Dim i As Integer
Dim source_row_number As Integer
Dim dest_row_number As Integer
Dim product As String
counter = Cells(Rows.Count, 1).End(xlUp).Row
source_row_number = 3
dest_row_number = 2
No_Of_Shpt = counter - 2

'Get User input here to repeat the items section with for loop
Dim p As Integer
Dim TotalPieces As Integer
TotalPieces = Sheets("Sheet2").Range("IT3").Value

For i = 1 To No_Of_Shpt

Sheets("Sheet1").Range("A" & dest_row_number).Value = counter
Sheets("Sheet1").Range("B" & dest_row_number).Value = "SHIP_FROM"

Sheets("Sheet1").Range("C" & dest_row_number).Value = Sheets("Sheet2").Range("B" & source_row_number).Value

Sheets("Sheet1").Range("D" & dest_row_number).Value = Sheets("Sheet2").Range("C" & source_row_number).Value

Sheets("Sheet1").Range("E" & dest_row_number).Value = Sheets("Sheet2").Range("D" & source_row_number).Value

*****ITEMS SECTION*****
Sheets("Sheet1").Range("A" & dest_row_number).Value = counter
Sheets("Sheet1").Range("B" & dest_row_number).Value = "PACKAGING"

    For p = 1 To TotalPieces
        
'Select the cell area to start adding more rows 
        Sheets("Sheet1").Select
        Sheets("Sheet1").Range("C5").Activate

        If p = 1 Then
            Worksheets("Sheet2").Range("BY3:CF3").Copy  'copy the first row item record and paste as first row record

            ActiveCell.PasteSpecial Paste:=xlPasteValues
    
        Else
'copy the first row item record and paste as 2nd item record in the next row.
            Worksheets("Sheet2").Range("BY3:CF3").Copy
            ActiveCell.Offset((p - 1) * 8, 0).Activate
            ActiveCell.PasteSpecial Paste:=xlPasteValues
            
            
        End If
        
    Next p
    
Application.CutCopyMode = False


'*****Continue Outer for loop*******
Sheets("Sheet1").Range("A" & dest_row_number).Value = counter
Sheets("Sheet1").Range("B" & dest_row_number).Value = "EXTRA_SERVICE"

Sheets("Sheet1").Range("C" & dest_row_number).Value = Sheets("Sheet2").Range("CG" & source_row_number).Value


Else

End If

'add counter to read next row from source
source_row_number = source_row_number + 1

'this is to add counter to write to next row
dest_row_number = dest_row_number + 1

counter = counter + 1

Next i

标签： excelvba