VBA Macro to auto insert row into Summary sheet from other sheet as source

Status
Not open for further replies.

eddorena

New Member
Joined
Dec 3, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Dear VBA Macro Experts,

Currently I am using a simple insert and delete macro from summary sheet, so when the source sheet, add a new line at the bottom, I'll click the Insert button from summary Sheet then drag from the above row to continue the formula that pick the value from the source sheet (I Attached the link of sample file)

My question is, what the correct VBA Macro that can make Auto insert into Summary sheet when I added new Row from source sheet, the summary sheet will auto insert and including the formula from above the line in the summary sheet (or maybe the Macro including to auto fill from the source)

I just confuse how to create the top and bottom gap to avoid auto insert overlapping (I mean, when I add a new row of the source sheet A, the summary from other source sheet not broken)

Thank you for the attention, Sorry If I can't delivered what I mean correctly since my English is not good enough
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi @Fluff m sorry for repost here

Hi Fluff, hope you are doing well and healthily.

Sorry to jump in on this thread, I think this is related to my cases.

I have VBA code below to insert and copy the formula above line, however, I feel still not working smoothly.

VBA Code:
Private Sub CommandButton1_Click()

On Error Resume Next
Dim iRow As Long
Dim iCount As Long
Dim i As Long

iCount = InputBox _
(Prompt:="Fill number line to insert")
iRow = InputBox _
(Prompt:="Choose the start line")

For i = 1 To iCount
ThisWorkbook.Sheets("Sheet2").Rows(iRow).EntireRow.Insert
ThisWorkbook.Sheets("Sheet2").Rows(Selection.Row - 1).Copy
ThisWorkbook.Sheets("Sheet1").Rows(iRow).EntireRow.Insert
ThisWorkbook.Sheets("Sheet1").Rows(Selection.Row - 1).Copy
Next i
End Sub

My expectation is, after click the button I fill the number of lines that need to insert then select the start line, however, the result it is not in accordance with expectation

Between Sheet1 and Sheet2 not same result, example :
I insert 5 lines, and start from line 3, in Sheet2 the result only 4 line ( 5 include the line2) and Sheet1 the result is correct.

Please your advise @Fluff, I Attached the file Sample

Thank You and Have a Nice Weekend
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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