VBA add specific data to certain cells after copy/paste range of rows.

[Valkyyrie]

I am very close on this. I need the code to prompt user for data that will need to be pasted into the newly inserted rows in columns b and c only. The code I have currently copies a range of rows from one sheet "Copy" and inserts those rows below the selected row in another sheet "WellDetail". I need it to do 2 things.
1 - This one is optional, but would save me time... It would be quicker if I didnt have to select the row which I'd like the "Copy" data inserted beneath. Not positive on how to make that work as the last active row where new rows will be inserted is variable as new rows are inserted into the sheet.
2 - I need data pasted into columns b and c of the newly inserted rows only. The data needing to be pasted into columns b and c are variable and should be based on user input.

Public Sub InsertCopiedRows()
On Error GoTo InsertCopiedCells_Error
'IT IS NECESSARY TO CALL THIS MACRO FROM THE SHEET "WellDetail"
'WITH A ROW SELECTED AND IS WHERE BELOW THIS ROW WILL BE
'INSERTED THE ROWS 2 TO 16 COPIED FROM THE SHEET "Copy"
Dim a As Integer
Sheets("Copy").Rows("2:16").Copy
Sheets("WellDetail").Select
ActiveCell.Offset(1, 0).Insert Shift:=xlDown
Application.CutCopyMode = False
On Error GoTo 0
Exit Sub
InsertCopiedCells_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertCopiedCells, line " & Erl & "."

End Sub

 

[Vogateer]

I'm anything but a guru, but this should get things pasted below the last used row in your WellDetail sheet:

    On Error GoTo InsertCopiedCells_Error
    'IT IS NECESSARY TO CALL THIS MACRO FROM THE SHEET "WellDetail"
    'WITH A ROW SELECTED AND IS WHERE BELOW THIS ROW WILL BE
    'INSERTED THE ROWS 2 TO 16 COPIED FROM THE SHEET "Copy"
    
    Dim a As Integer ' what is this doing here?
    Dim paste_row As Long
    Dim copy_range As Range
    Set copy_range = Sheets("Copy").Rows("2:16")
    
    ' Find the last row in the WellDetail sheet, and paste below
    With Sheets("WellDetail")
        paste_row = .Cells.Find(What:="*", _
                                After:=Range("A1"), _
                                searchorder:=xlByRows, _
                                searchdirection:=xlPrevious).Row + 1
        ' Now copy and paste with one action
        copy_range.Copy .Range(Cells(paste_row, 1), Cells(paste_row + copy_range.Rows.Count, copy_range.Columns.Count))
        End With

    ActiveCell.Offset(1, 0).Insert Shift:=xlDown ' Not sure what this is for
    Application.CutCopyMode = False
    On Error GoTo 0
    Exit Sub

InsertCopiedCells_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertCopiedCells, line " & Erl & "."
    
    End Sub

I would need more information about how you want the user to manually enter the information for columns b & c. Would you want them to enter the information in a pop up window for each row? Can they just enter the information in the sheet directly? It starts to get a bit harder when you want to create a userform or something like that.

 

[Vogateer]

Sub  InsertCopiedRows()
    On Error GoTo InsertCopiedCells_Error
    'IT IS NECESSARY TO CALL THIS MACRO FROM THE SHEET "WellDetail"
    'WITH A ROW SELECTED AND IS WHERE BELOW THIS ROW WILL BE
    'INSERTED THE ROWS 2 TO 16 COPIED FROM THE SHEET "Copy"
   
    Dim a As Integer ' what is this doing here?
    Dim paste_row As Long
    Dim copy_range As Range
    Set copy_range = Sheets("Copy").Rows("2:16")
   
    ' Find the last row in the WellDetail sheet, and paste below
    With Sheets("WellDetail")
        paste_row = .Cells.Find(What:="*", _
                                After:=Range("A1"), _
                                searchorder:=xlByRows, _
                                searchdirection:=xlPrevious).Row + 1
        ' Now copy and paste with one action
        copy_range.Copy .Range(Cells(paste_row, 1), Cells(paste_row + copy_range.Rows.Count, copy_range.Columns.Count))
        End With

    ActiveCell.Offset(1, 0).Insert Shift:=xlDown ' Not sure what this is for
    Application.CutCopyMode = False
    On Error GoTo 0
    Exit Sub

InsertCopiedCells_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertCopiedCells, line " & Erl & "."
   
    End Sub

I would need more information about how you want the user to manually enter the information for columns b & c. Would you want them to enter the information in a pop up window for each row? Can they just enter the information in the sheet directly? It starts to get a bit harder when you want to create a userform or something like that.

Not sure why it left off the initial Sub declaration. Oh, and in this version it doesn't matter where the macro is called, because it's explicitly copying from the Copy sheet and pasting into the WellDetail sheet.

 

[Vogateer]

Oh, I didn't see the images initially on my phone. If simple input boxes are enough, try this and see what you think. Ignore the ones above that are missing the dots before "Cells"

Sub InsertCopiedRows()
    'On Error GoTo InsertCopiedCells_Error
    'IT IS NECESSARY TO CALL THIS MACRO FROM THE SHEET "WellDetail"
    'WITH A ROW SELECTED AND IS WHERE BELOW THIS ROW WILL BE
    'INSERTED THE ROWS 2 TO 16 COPIED FROM THE SHEET "Copy"
    
    Dim a As Integer ' What is this doing here? Doesn't seem to be used.
    Dim paste_row As Long
    Dim copy_range As Range
    Set copy_range = Sheets("Copy").Rows("2:16")
    Dim group_number As Long, serial_number As Long
    
    ' Find the last row in the WellDetail sheet, and paste below
    With Sheets("WellDetail")
        paste_row = .Cells.Find(What:="*", _
                                After:=Range("A1"), _
                                searchorder:=xlByRows, _
                                searchdirection:=xlPrevious).Row + 1
        ' Now copy and paste with one action
        copy_range.Copy .Range(.Cells(paste_row, 1), .Cells(paste_row + copy_range.Rows.Count, copy_range.Columns.Count))
        
        ' Get group number and serial number from the user
        group_number = Application.InputBox("Enter Group Number", "Group Number Entry")
        serial_number = Application.InputBox("Serial Group Number", "Serial Number Entry")
        
        Debug.Print "Trying to sort this out"
        ' Enter those into columns 2 (group) and 3 (serial)
        .Range(.Cells(paste_row, 2), .Cells(paste_row + copy_range.Rows.Count - 1, 2)).Value = group_number
        .Range(.Cells(paste_row, 3), .Cells(paste_row + copy_range.Rows.Count - 1, 3)).Value = serial_number
        
        End With

    ActiveCell.Offset(1, 0).Insert Shift:=xlDown ' Not sure what this is for
    Application.CutCopyMode = False
    On Error GoTo 0
    Exit Sub

InsertCopiedCells_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertCopiedCells, line " & Erl & "."
    
    End Sub

 

[Valkyyrie]

Oh, I didn't see the images initially on my phone. If simple input boxes are enough, try this and see what you think. Ignore the ones above that are missing the dots before "Cells"

Sub InsertCopiedRows()
    'On Error GoTo InsertCopiedCells_Error
    'IT IS NECESSARY TO CALL THIS MACRO FROM THE SHEET "WellDetail"
    'WITH A ROW SELECTED AND IS WHERE BELOW THIS ROW WILL BE
    'INSERTED THE ROWS 2 TO 16 COPIED FROM THE SHEET "Copy"
   
    Dim a As Integer ' What is this doing here? Doesn't seem to be used.
    Dim paste_row As Long
    Dim copy_range As Range
    Set copy_range = Sheets("Copy").Rows("2:16")
    Dim group_number As Long, serial_number As Long
   
    ' Find the last row in the WellDetail sheet, and paste below
    With Sheets("WellDetail")
        paste_row = .Cells.Find(What:="*", _
                                After:=Range("A1"), _
                                searchorder:=xlByRows, _
                                searchdirection:=xlPrevious).Row + 1
        ' Now copy and paste with one action
        copy_range.Copy .Range(.Cells(paste_row, 1), .Cells(paste_row + copy_range.Rows.Count, copy_range.Columns.Count))
       
        ' Get group number and serial number from the user
        group_number = Application.InputBox("Enter Group Number", "Group Number Entry")
        serial_number = Application.InputBox("Serial Group Number", "Serial Number Entry")
       
        Debug.Print "Trying to sort this out"
        ' Enter those into columns 2 (group) and 3 (serial)
        .Range(.Cells(paste_row, 2), .Cells(paste_row + copy_range.Rows.Count - 1, 2)).Value = group_number
        .Range(.Cells(paste_row, 3), .Cells(paste_row + copy_range.Rows.Count - 1, 3)).Value = serial_number
       
        End With

    ActiveCell.Offset(1, 0).Insert Shift:=xlDown ' Not sure what this is for
    Application.CutCopyMode = False
    On Error GoTo 0
    Exit Sub

InsertCopiedCells_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertCopiedCells, line " & Erl & "."
   
    End Sub

Thank you! This worked perfectly! Removed the code that wasnt needed. What I ended up with is below.

Sub InsertCopiedRows()
On Error GoTo InsertCopiedCells_Error
    'This will copy rows 2 to 16 from the "Copy" sheet and
    'Insert them after the last row in the "WellDetail"sheet
    'Then prompt user to enter a group and serial number
        
    Dim paste_row As Long
    Dim copy_range As Range
    Set copy_range = Sheets("Copy").Rows("2:16")
    Dim group_number As Long, serial_number As Long
    
    ' Find the last row in the WellDetail sheet, and paste below
    With Sheets("WellDetail")
        paste_row = .Cells.Find(What:="*", _
                                After:=Range("A1"), _
                                searchorder:=xlByRows, _
                                searchdirection:=xlPrevious).Row + 1
        ' Now copy and paste with one action
        copy_range.Copy .Range(.Cells(paste_row, 1), .Cells(paste_row + copy_range.Rows.Count, copy_range.Columns.Count))
        
        ' Get group number and serial number from the user
        group_number = Application.InputBox("Enter Group Number", "Group Number Entry")
        serial_number = Application.InputBox("Serial Group Number", "Serial Number Entry")
        
        Debug.Print "Trying to sort this out"
        ' Enter those into columns 2 (group) and 3 (serial)
        .Range(.Cells(paste_row, 2), .Cells(paste_row + copy_range.Rows.Count - 1, 2)).Value = group_number
        .Range(.Cells(paste_row, 3), .Cells(paste_row + copy_range.Rows.Count - 1, 3)).Value = serial_number
        
        End With

    Application.CutCopyMode = False
    On Error GoTo 0
    Exit Sub

InsertCopiedCells_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertCopiedCells, line " & Erl & "."
    
    End Sub

 

[Vogateer]

Excellent! If you're using that, please note that I meant "Serial Group Number" to be "Enter Serial Number". I don't want to cause any confusion for users with that.

 

[Valkyyrie]

Excellent! If you're using that, please note that I meant "Serial Group Number" to be "Enter Serial Number". I don't want to cause any confusion for users with that.

I realized that when I was testing it out and edited the text to read "Enter Serial Number". Thank you so much for your help! This has saved hours of work for my team.