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

Question

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:

Sub SubmitPlan()

    'NAME
        Sheets("Input").Select
        Range("D7").Select
        Selection.Copy
        Sheets("Input Data").Select
        Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    'HIREDATE
        Sheets("Input").Select
        Range("G7:H7").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Input Data").Select
        ActiveCell.Offset(0,1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    'TRAINEETYPE
        Sheets("Input").Select
        Range("D10").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Input Data").Select
        ActiveCell.Offset(0,1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    'VERIFY
        Sheets("Input").Select
        Range("B15").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Input Data").Select
        Range("D1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    'LOOP REMAINING COLUMNS
    For each cell in rng    
        Sheets("Input").Select
        ActiveCell.Offset(0,1).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Input Data").Select
        ActiveCell.Offset(0,1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Thanks for your help:)

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

To start with you need a `Next` to go with the `For`, see [here](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fornext-statement). Also, you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Jun 08 '23 at 19:36

score 1 · Answer 1 · answered Jun 08 '23 at 20:23

1

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

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

answered Jun 08 '23 at 20:23

z32a7ul

3,695
3
21
45

Hi, thanks for your help. This code is very elegant. However, it won't even run. Perhaps it is missing something? Can you help further? I just copied your code exactly.... – ChristineLovesData Jun 09 '23 at 13:57
What error message do you get? – z32a7ul Jun 09 '23 at 14:08
None, it wouldn't run at all – ChristineLovesData Jun 09 '23 at 23:15
OK, so you had a Workbook, where your original macro did run, right? Then you deleted the old one and inserted the new one where it was, right? You did the same thing that triggered the old macro (like pressing a button) but nothing happened: no error message and no copying of data, right? Could you please press [F9] while standing on line starting with `Dim wsInput` to insert a breakpoint and try to run the macro again? Did a yellow arrow appear at that line? – z32a7ul Jun 10 '23 at 07:05
This part appears red: : rowData = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row – ChristineLovesData Jun 10 '23 at 20:04
Red is the color of the line with breakpoint. Maybe you set the breakpoint on that line. Press [F5] and see what happens. – z32a7ul Jun 10 '23 at 20:15
Nothing happens. So frustrated. I really appreciate your help. I don't get it. Is there any possibility for more help? – ChristineLovesData Jun 11 '23 at 18:39
When I step into, this area does not turn yellow but goes to the end sub: rowData = rowData + 1 until Next – ChristineLovesData Jun 11 '23 at 18:57
I recreated my workbook and your code worked perfectly. Thanks for everything!! – ChristineLovesData Jun 11 '23 at 23:03
I'm glad to hear that. Could you please mark this answer as accepted with the check mark? – z32a7ul Jun 12 '23 at 19:35

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

1 Answers1