VBA Inserting/Deleting rows

[HazatB]

Hello,

I have set up the following code in VBA to insert rows into my table.
Here is the code

Dim myLastRow As Long

myLastRow = Worksheets("Before n After Remap Review").Range("A7").End(xlDown).Row

MsgBox prompt:="The last row is Row No. " & myLastRow

Dim x As Integer
x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown

Dim strSum As String
Dim LR As Long

strSum = "=SUM($A$8:$A$@LR)"
LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(LR, 1).Resize(, 5).Formula = Replace(strSum, "@LR", LR)

I need to adjust this code to be able to add or delete rows without a prompt based on the new data I am inputting. Could anyone please assist.

 

[Logit]

.
Which prompt are you wanting to delete ?

The first is a message box which advises how many rows exist (used rows with data). Deleting that prompt is easy ... place a hyphen ( ' ) in front of the line of code or delete the entire line of code.

Getting rid of the remaining Input Boxes will negate the use of the macro .... the Input Boxes provide the information needed by the macro to give you the extra lines. You can 'hardcode' the values
provided by the Input Boxes but keep in mind doing so will make changing the selection difficult as you will need to go into the macro code and change the information.

???

 

[HazatB]

.
Which prompt are you wanting to delete ?

The first is a message box which advises how many rows exist (used rows with data). Deleting that prompt is easy ... place a hyphen ( ' ) in front of the line of code or delete the entire line of code.

Getting rid of the remaining Input Boxes will negate the use of the macro .... the Input Boxes provide the information needed by the macro to give you the extra lines. You can 'hardcode' the values
provided by the Input Boxes but keep in mind doing so will make changing the selection difficult as you will need to go into the macro code and change the information.

???

The first message box is fine and I can delete that. What I am looking to do is to be able to copy+paste data and adjust for the number of rows.

 

[Logit]

.

What I am looking to do is to be able to copy+paste data and adjust for the number of rows.

This is a different direction than the title would imply.

It would be best if you could post a sample "Before and After" example of what you are seeking with an explanation ... or at a minimum, an accurate description that will
avoid confusion.

 

[HazatB]

.


This is a different direction than the title would imply.

It would be best if you could post a sample "Before and After" example of what you are seeking with an explanation ... or at a minimum, an accurate description that will
avoid confusion.

Yes sorry for the confusion. I have re-written the code to best suit what I am attempting to achieve featured below.

Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("A1:E6651").Copy
Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("A7:E6657").PasteSpecial

Application.CutCopyMode = False

Workbooks("before_n_after_remap_audit_umroi.txt").Worksheets("before_n_after_remap_audit_umro").Range("F1:G6651").Copy
Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Before n After Remap Review").Range("I7:J6657").PasteSpecial

Application.CutCopyMode = False

This code above has gotten me the desired base of what I am attempting to accomplish but what I need to figure out is when the rows change to less/more then the amount I specified here.