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

V

Valkyyrie

New Member
Joined
Jun 15, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code: 
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
 

Attachments

  • Copy Sheet.png
    Copy Sheet.png
    8.3 KB · Views: 14
  • WellDetail Sheet.png
    WellDetail Sheet.png
    11.9 KB · Views: 11

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
I'm anything but a guru, but this should get things pasted below the last used row in your WellDetail sheet:
VBA Code: 
    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.
 
Upvote 0
Vogateer said:
VBA Code: 
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.
Click to expand...

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.
 
Upvote 0
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"

VBA Code: 
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
 
Upvote 1
Solution
Vogateer said:
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"

VBA Code: 
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
Click to expand...
Thank you! This worked perfectly! Removed the code that wasnt needed. What I ended up with is below.

VBA Code: 
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
 
Upvote 0
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.
 
Upvote 0
Vogateer said:
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.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Code that locates a specific character string in a range, and then copies the row that its in and pastes it beneath it...
Replies
5
Views
392
Kerryx
K
T
  • Solved
VBA code cannot find sheet name
2
Replies
11
Views
184
jolivanes
jolivanes
W
  • Solved
Adding Rows & Copying Data in Row Above
2
Replies
11
Views
341
Wilconcl51
W
WERNER SLABBERT
  • Question
copy and create problem
Replies
0
Views
162
WERNER SLABBERT
WERNER SLABBERT
G
  • Solved
Add total row with a loop
Replies
4
Views
152
gberg
G

Forum statistics

Threads
1,215,069
Messages
6,122,956
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top