Need help for inserting rows & worksheets on button click using VBA

**saket_47** · 10-31-2014, 04:31 AM

Attached - Workbook_001.xlsm (for reference.)

Hi, I am novice in VBA application for Excel.
I need your help in creating VBA (.xlxm) file that will do tasks as detailed below.

i) The Workbook contains One “Summary” sheet & one “Master” sheet.
“Summary” sheet has table with Serial number, reference No., Description etc.
“Master” sheet will be used for making duplicate multiple copies as required.
ii) On click of a button placed in “Summary” sheet, -
1. One Row should get added to the table with automatic Next serial number & Next Reference number in series.
2. After Row is added to the Worksheet, “Master” worksheet should make a copy of itself at the end of all worksheets.
3. This newly added Worksheet should be renamed to Reference number from Row added in “Summary” sheet
4. Defined Cells (e.g. A2 or B2 etc) inside this newly added worksheet should reflect values from newly added row in Summary sheet.
(i.e. Serial Number & Reference Number generated in Summary sheet should also go into Copied Master sheet at specified cells (A2,B2)
5. After the text is manually entered in “Description” column of “Summary” sheet, it should reflect in respective duplicated “Master” Worksheets at specified cells (C2)
6. If possible, I want to keep “Master” Sheet hidden.

**natefarm** · 10-31-2014, 05:32 PM

Try this. You'll want to move your Add button up above the data.

Private Sub Add_Row_And_Worksheet_Click()
Dim rw As Long, sname As String

    Application.ScreenUpdating = False
    rw = Range("B3").End(xlDown).Row + 1
    Rows(rw - 1).Copy
    Rows(rw).PasteSpecial (xlPasteFormats)
    Application.CutCopyMode = False
    Cells(rw, 2).Value = Cells(rw - 1, 2).Value + 1
    sname = "REF" & Format(Right(Cells(rw - 1, 3).Value, Len(Cells(rw - 1, 3).Value) - 3) + 1, "00")
    Cells(rw, 3).Value = sname

    With ActiveWorkbook.Sheets("Master")
        .Visible = True
        .Copy after:=Sheets(Sheets.Count)
        .Visible = False
    End With

    With ActiveSheet
        .Name = sname
        .Range("A2").Formula = "=Summary!B" & rw
        .Range("B2").Formula = "=Summary!C" & rw
        .Range("C2").Formula = "=Summary!D" & rw
    End With
    Sheets("Summary").Activate
End Sub

**saket_47** · 11-02-2014, 11:27 PM

Dear natefarm,
Thanks a lot. It worked perfectly as required.

Is it possible that the button to be positioned at the end of the table & when row is inserted button will also move down by a row.

**natefarm** · 11-03-2014, 11:13 AM

Yes. In the above code, change this:

    Rows(rw - 1).Copy

to this:

    Rows(rw).Insert
    Rows(rw - 1).Copy

**saket_47** · 11-03-2014, 11:45 PM

Thanks again.

Need help for inserting rows & worksheets on button click using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need help for inserting rows & worksheets on button click using VBA

Re: Need help for inserting rows & worksheets on button click using VBA

Re: Need help for inserting rows & worksheets on button click using VBA

Re: Need help for inserting rows & worksheets on button click using VBA

Re: Need help for inserting rows & worksheets on button click using VBA

Thread Information

Users Browsing this Thread

Similar Threads

How to clear contents/delete rows on click of button

Command Button to change the subsequent rows with each click

inserting rows with submit button

Printing worksheets with a click of a button?

copy 45 rows down, repetativly, only on button click?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions