Copy + insert row / add new sheet and incrementally add more

elmogm

New Member
Joined
May 30, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi, Firstly apologies if I post incorrectly, I have no idea how to code.

I have a workbook that has a front summary sheet and a detail sheet. The front sheet shows important data from the detail sheet.
The code below is that when I click the 'click to add part' button, it will copy and paste row 10 to row 11, and also add a new sheet called (2).
If I then click the button again it will add sheet (3) and so on.

1st question - how do I get the copied row on summary sheet to update to the new sheet. Eg cell A10='(1)'!$D$3, how can I get A11 to change to A11='(2)'!$D$3 when I copy and paste it? I need to do this to all cells in the row.

2nd question - if I click the button again how can I get the row to copy and paste incrementally. Currently it inserts to row 11, how can I get the next one to copy to row 12 and also update the new rows to link to sheet (3)?

123456.png


VBA Code:
Sub Sample()

    Application.CutCopyMode = False

    With Worksheets("SUMMARY")

    .Rows(10).Copy
    .Rows(11).Insert Shift:=xlShiftDown

    End With

    Application.CutCopyMode = True

    Dim test As Worksheet
    Sheets(2).Copy After:=Sheets(Sheets.Count)
    Set test = Sheets(Sheets.Count)
    test.Name = "copied sheet!"
    
End Sub

Hope this makes some sense, any help or point to threads that previously answer similar greatly appreciated. I have searched for answers but can't find anything I can get to work.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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