I’m creating a template for work, and I need to be able to insert a variable range from an input table which sits in “Inputs” into a form sheet, which sits in “Form”. The input table currently has a large preset array so that the user doesn’t have to change the range themselves (I work with those some might call “unskilled” at excel), and their inputs could range from anywhere from 2-75+ rows.

Again, these inputs need to then be inserted into “Form”, part of which is a printed form. Every cell above row 15 is hard coded and needs to be preserved, similarly, every cell below row 18 is hard coded and also needs to be preserved. That leaves rows 16 and 17 to be the basis for insertion of the data. My thinking is that this will help prevent errors, however if there is a better solution please advise.

To make things more complicated, I only need to draw from certain parts of the table. In this situation: Columns 1, 2, 3, and 7 (“Inputs”, Columns B, C, D, H). Respectively, these columns will be inserted into “Form”, columns A, B, D, E.

Lastly, I need to perform two operations on this variable range in “Form” Column F, where F multiplies column E by a constant.

In my attempts, I’ve only been trying to insert the variables and have failed thus far. I’ve added in some error handling to help troubleshoot but it isn’t really working. When I run the code, I get a return message stating the data has been inserted into “Form” but nothing populates.

Below is a reference image for aid.

Below is my code:

Sub InsertDataIntoForm()
    Dim wsInputs As Worksheet
    Dim wsForm As Worksheet
    Dim lastRow As Long, numRows As Long
    Dim i As Long
    
    ' Set worksheet objects
    Set wsInputs = ThisWorkbook.Worksheets("Inputs")
    Set wsForm = ThisWorkbook.Worksheets("Form")
    
    ' Find the last row with data in column B of "Inputs"
    lastRow = wsInputs.Cells(wsInputs.Rows.Count, "B").End(xlUp).Row
    
    ' Calculate number of rows to copy
    numRows = lastRow - 15  ' Start from row 16
    
    ' Copy data from Inputs to Form
    For i = 1 To numRows
        ' Copy columns B, C, D, and H from Inputs to columns A, B, D, and E in Form
        wsForm.Cells(i + 15, "A").Value = wsInputs.Cells(i + 15, "B").Value  ' Column B in Inputs to Column A in Form
        wsForm.Cells(i + 15, "B").Value = wsInputs.Cells(i + 15, "C").Value  ' Column C in Inputs to Column B in Form
        wsForm.Cells(i + 15, "D").Value = wsInputs.Cells(i + 15, "D").Value  ' Column D in Inputs to Column D in Form
        wsForm.Cells(i + 15, "E").Value = wsInputs.Cells(i + 15, "H").Value  ' Column H in Inputs to Column E in Form
    Next i
    
    ' Notify user that data has been inserted
    MsgBox "Data has been inserted into the Form sheet."
End Sub

I’m not the most experienced VBA programmer – I’m self taught and this is my second month using it. I’ve been using a composite of youtube, microsoft support research, and ChatGPT to try and help. Unfortunately, this is the best I can muster thus far.

In my attempts I’ve tried changing all the variables, I’ve tried creating new variables for the ranges, I’ve thought about creating new methods to do this and I have one in mind (assigning a preset range to the form sheet then using VBA to remove empty rows, however I think I would like to try and make this work for my education’s sake. If the challenge is too difficult, I understand however.

If I have left out any important/relevant/pertinent information, please feel free to ask. I will do my best to respond in a timely manner.

EDIT 7/17/2024

Below is revised code to reflect updates (Thanks to @Blackcat). This code works well for inserting the data, however I’m still needing to address the issue of removing unnecessary rows that are empty from the sheet.

Sub InsertDataIntoForm()
    Dim wsInputs As Worksheet
    Dim wsForm As Worksheet
    Dim lastRow As Long, numRows As Long
    Dim i As Long
    
    ' Set worksheet objects
    Set wsInputs = ThisWorkbook.Worksheets("Inputs")
    Set wsForm = ThisWorkbook.Worksheets("Form")
    
    ' Find the last row with data in column B of "Inputs"
    lastRow = wsInputs.Cells(wsInputs.Rows.Count, "B").End(xlUp).Row
    
    ' Calculate number of rows to copy
    numRows = lastRow - 15  ' Start from row 16
    Dim mulconst As Double
    mulconst = 1.2   'the constant to multiply
    
    ' Copy data from Inputs to Form
    For i = 1 To numRows
    
        ' Copy columns B, C, D, and H from Inputs to columns A, B, D, and E in Form
        If i > 2 Then
          wsForm.Rows(i + 15).Insert   'insert row to make space for the new values
        End If
        
        wsForm.Cells(i + 15, "A").Value = wsInputs.Cells(i + 7, "B").Value  ' Column B in Inputs to Column A in Form
        wsForm.Cells(i + 15, "B").Value = wsInputs.Cells(i + 7, "C").Value  ' Column C in Inputs to Column B in Form
        wsForm.Cells(i + 15, "D").Value = wsInputs.Cells(i + 7, "D").Value  ' Column D in Inputs to Column D in Form
        wsForm.Cells(i + 15, "E").Value = wsInputs.Cells(i + 7, "H").Value  ' Column H in Inputs to Column E in Form
        wsForm.Cells(i + 15, "F").Value = wsInputs.Cells(i + 7, "E") * mulconst   'calculate and set the value for column F

    Next i
    
    ' Notify user that data has been inserted
    MsgBox "Data has been inserted into the Form sheet."
End Sub