Excel VBA循环遍历格式化的表单并将数据粘贴到表格中。

huangapple
go评论96阅读模式
英文:

Excel VBA Loop through a formatted form and paste the data into a table

问题

I have two worksheets in Excel, one is a formatted form named INPUT and that I want a user to enter their training plan and press a button to paste the records into a table on another worksheet named INPUT DATA. The process will repeat for new hires by adding them to the table without overwriting.
Example

Can someone please explain how to loop the code? Here is the VBA code that I have:

  1. Sub SubmitPlan()
  3.     'NAME
  4.         Sheets("Input").Select
  5.         Range("D7").Select
  6.         Selection.Copy
  7.         Sheets("Input Data").Select
  8.         Range("A1").Select
  9.         Selection.End(xlDown).Select
  10.         ActiveCell.Offset(1).Select
  11.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  12.             :=False, Transpose:=False
  13.     'HIREDATE
  14.         Sheets("Input").Select
  15.         Range("G7:H7").Select
  16.         Application.CutCopyMode = False
  17.         Selection.Copy
  18.         Sheets("Input Data").Select
  19.         ActiveCell.Offset(0,1).Select
  20.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  21.             :=False, Transpose:=False
  22.     'TRAINEETYPE
  23.         Sheets("Input").Select
  24.         Range("D10").Select
  25.         Application.CutCopyMode = False
  26.         Selection.Copy
  27.         Sheets("Input Data").Select
  28.         ActiveCell.Offset(0,1).Select
  29.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  30.             :=False, Transpose:=False
  31.     'VERIFY
  32.         Sheets("Input").Select
  33.         Range("B15").Select
  34.         Application.CutCopyMode = False
  35.         Selection.Copy
  36.         Sheets("Input Data").Select
  37.         Range("D1").Select
  38.         Selection.End(xlDown).Select
  39.         ActiveCell.Offset(1).Select
  40.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  41.             :=False, Transpose:=False
  42.     'LOOP REMAINING COLUMNS
  43. 	For each cell in rng    
  44. 	    Sheets("Input").Select
  45. 	    ActiveCell.Offset(0,1).Select
  46. 	    Application.CutCopyMode = False
  47. 	    Selection.Copy
  48. 	    Sheets("Input Data").Select
  49. 	    ActiveCell.Offset(0,1).Select
  50. 	    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  51. 		:=False, Transpose:=False
  52. End Sub

Thanks for your help:)

I've tried so many things but can't figure it out

英文:

I have two worksheets in Excel, one is a formatted form named INPUT and that I want a user to enter their training plan and press a button to paste the records into a table on another worksheet named INPUT DATA. The process will repeat for new hires by adding them to the table without overwriting.
Example

Can someone please explain how to loop the code? Here is the VBA code that I have:

  1. Sub SubmitPlan()
  3.     'NAME
  4.         Sheets("Input").Select
  5.         Range("D7").Select
  6.         Selection.Copy
  7.         Sheets("Input Data").Select
  8.         Range("A1").Select
  9.         Selection.End(xlDown).Select
  10.         ActiveCell.Offset(1).Select
  11.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  12.             :=False, Transpose:=False
  13.     'HIREDATE
  14.         Sheets("Input").Select
  15.         Range("G7:H7").Select
  16.         Application.CutCopyMode = False
  17.         Selection.Copy
  18.         Sheets("Input Data").Select
  19.         ActiveCell.Offset(0,1).Select
  20.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  21.             :=False, Transpose:=False
  22.     'TRAINEETYPE
  23.         Sheets("Input").Select
  24.         Range("D10").Select
  25.         Application.CutCopyMode = False
  26.         Selection.Copy
  27.         Sheets("Input Data").Select
  28.         ActiveCell.Offset(0,1).Select
  29.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  30.             :=False, Transpose:=False
  31.     'VERIFY
  32.         Sheets("Input").Select
  33.         Range("B15").Select
  34.         Application.CutCopyMode = False
  35.         Selection.Copy
  36.         Sheets("Input Data").Select
  37.         Range("D1").Select
  38.         Selection.End(xlDown).Select
  39.         ActiveCell.Offset(1).Select
  40.         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  41.             :=False, Transpose:=False
  42.     'LOOP REMAINING COLUMNS
  43. 	For each cell in rng    
  44. 	    Sheets("Input").Select
  45. 	    ActiveCell.Offset(0,1).Select
  46. 	    Application.CutCopyMode = False
  47. 	    Selection.Copy
  48. 	    Sheets("Input Data").Select
  49. 	    ActiveCell.Offset(0,1).Select
  50. 	    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  51. 		:=False, Transpose:=False
  52. End Sub

Thanks for your help:)

I've tried so many things but can't figure it out

答案1

得分: 1

根据截图，我会这样做：

  1. Public Sub SubmitPlan()
  2.     Dim wsInput As Worksheet: Set wsInput = ThisWorkbook.Worksheets("Input")
  3.     Dim wsData As Worksheet: Set wsData = ThisWorkbook.Worksheets("Input Data")
  4.     Dim rowData As Long: rowData = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
  5.     Dim rowInput As Long: For rowInput = 15 To wsInput.Cells(wsInput.Rows.Count, 1).End(xlUp).Row
  6.         rowData = rowData + 1
  7.         wsData.Cells(rowData, "A").Value = wsInput.Range("B7").Value ' 名称
  8.         wsData.Cells(rowData, "B").Value = wsInput.Range("G7").Value ' 入职日期
  9.         wsData.Cells(rowData, "C").Value = wsInput.Range("D10").Value ' 培训类型
  10.         wsData.Cells(rowData, "D").Value = wsInput.Cells(rowInput, "B").Value ' 已验证
  11.         wsData.Cells(rowData, "E").Value = wsInput.Cells(rowInput, "C").Value ' 课程编号
  12.         wsData.Cells(rowData, "F").Value = wsInput.Cells(rowInput, "D").Value ' 课程标题
  13.         wsData.Cells(rowData, "G").Value = wsInput.Cells(rowInput, "E").Value ' 工作坊时长
  14.         wsData.Cells(rowData, "H").Value = wsInput.Cells(rowInput, "F").Value ' 项目执行
  15.         wsData.Cells(rowData, "I").Value = wsInput.Cells(rowInput, "G").Value ' 工作坊开始日期
  16.         wsData.Cells(rowData, "J").Value = wsInput.Cells(rowInput, "H").Value ' 项目开始日期
  17.     Next
  18. End Sub

希望这对你有所帮助！

英文:

Based on the screenshot, I would do it like this:

  1. Public Sub SubmitPlan()
  2.     Dim wsInput As Worksheet: Set wsInput = ThisWorkbook.Worksheets("Input")
  3.     Dim wsData As Worksheet: Set wsData = ThisWorkbook.Worksheets("Input Data")
  4.     Dim rowData As Long: rowData = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
  5.     Dim rowInput As Long: For rowInput = 15 To wsInput.Cells(wsInput.Rows.Count, 1).End(xlUp).Row
  6.         rowData = rowData + 1
  7.         wsData.Cells(rowData, "A").Value = wsInput.Range("B7").Value ' Name
  8.         wsData.Cells(rowData, "B").Value = wsInput.Range("G7").Value ' Hire Date
  9.         wsData.Cells(rowData, "C").Value = wsInput.Range("D10").Value ' Trainee Type
  10.         wsData.Cells(rowData, "D").Value = wsInput.Cells(rowInput, "B").Value ' Verified
  11.         wsData.Cells(rowData, "E").Value = wsInput.Cells(rowInput, "C").Value ' Course Number
  12.         wsData.Cells(rowData, "F").Value = wsInput.Cells(rowInput, "D").Value ' Course Title
  13.         wsData.Cells(rowData, "G").Value = wsInput.Cells(rowInput, "E").Value ' Workshop Duration
  14.         wsData.Cells(rowData, "H").Value = wsInput.Cells(rowInput, "F").Value ' Project Execution
  15.         wsData.Cells(rowData, "I").Value = wsInput.Cells(rowInput, "G").Value ' Workshop Start Date
  16.         wsData.Cells(rowData, "J").Value = wsInput.Cells(rowInput, "H").Value ' Project Start Date
  17.     Next
  18. End Sub

huangapple
  • 本文由 发表于 2023年6月9日 03:21:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/76435083.html
  • excel
  • forms
  • loops
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定