Need a bit of assistance with a macro for inserting rows

[valenuk1]

Hi there...I’m hoping someone can help me out with a macro im trying to create.

First of all….a bit of background.

I'm currently in the process of finalising a workbook that I’ve been developing, which I will be using to analysis the financial performance of a number of suppliers.

So far the workbook contains a couple of summary pages and around 50 worksheets that are generically the same layout & formatting. Each of the 50 worksheets are then used to capture specific information for a particular supplier.

Certain data entries (supplier names, sub totals, dates, etc etc) on the worksheets are absolute referenced on the summary sheets, and all of the worksheets are password protected to safeguard formulas, with certain cells left unprotected to allow user input.


So…..based on the above background, here’s my current dilemma

I’m trying to develop a worksheet specific set of macros, which will allow a user to insert rows (complete with formatting and formulas). Each worksheet needs five macros to insert rows in specific locations (i.e. the last lines before a five individual subtotal of a sub-section, so that they then import into an overall sub-total of a section).

A jpg image of one of these typical worksheets has been posted here

http://i28.photobucket.com/albums/c201/valenuk1/SCEFC.jpg


So far I have been able to record a macro to carry out this task on worksheet, as detailed below. I have also indicated where the macro buttons will be, whereby I wish to provide a user the facility to insert a row.

My macro so far is as follows.

Sub InsertRow1()

Row1 Macro
Macro recorded 16/11/2005

ActiveSheet.Unprotect Password:="XXXX"
ActiveWindow.LargeScroll Down:=1
Rows("37:37").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("E38").Select
ActiveSheet.Protect Password:="XXXX"

End Sub

I know that I will have develop 4 more parts to this macro to cater for the buttons that are indicated on the jpg image, but the overall problem Im having is that the macro is applied to the entire workbook, which is not exactly what I need as the sub-totals for each section will appear in different locations on the other 49 sheets, which is fully dependant on the amount of info that is inserted by the user onto the worksheets.

Therefore I need to understand how to develop a worksheet specific set of macro’s

I know that above may have rambled on a bit, but I thought it best to explain the assistance that I’m hoping someone here will be able to provide me with.

If you need to know anything else, please leave a PM.

Thanks in advance

Valenuk1....

 

[Norie]

That code is worksheet specific.

In fact it is specific to the currently active worksheet because there are no references to any other worksheet.

 

[HalfAce]

Hello valenuk1, welcome to the board.
Am I correct in thinking that all (50 or so) sheets will get their own set of 5 buttons, and that your problem is that you don't want to hard code which row gets copied/inserted (because as you said, the sheets will have a varying number of rows with data)?
If that's the case (and the button(s) will be clicked from one of the actual 50 or so sheets) then I believe you would want the code to be non sheet specific and work on the active sheet as you have now. The only problem then would be how to determine which row to perform the code on in any of these 50 sheets. (Is this anywhere near right?)

If so, which row are you looking to copy / insert? (ie, the last row?, the 5th row up from the last row?, ...etc.)

If my assumptions are correct, and if you were in fact looking to perform this on the last row of data in a sheet (no matter what row that may be) then perhaps this example might help. (It will work on whatever sheet is the active sheet at the time, meaning you could assign this code to a (Forms toolbar) button and simply copy & paste the button to all the other sheets.)

Sub GimmeOneMoreRow()
Dim LstRw As Long

LstRw = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

ActiveSheet.Unprotect Password:="XXXX"
Rows(LstRw).Copy
Rows(LstRw).Insert Shift:=xlDown
Application.CutCopyMode = False
Cells(LstRw + 1, 5).Select
ActiveSheet.Protect Password:="XXXX"
End Sub

Hope it helps. Let us know if I'm off track and this is not what you meant.

 

[valenuk1]

Thanks for the responses peeps.......

Thanks for the response HalfAce (and Norie..even though I was left scratching my head..lol)

You are correct HalfAce, each of the 50ish worksheets will get their own set of five buttons.

Not to sure about the hard code (kind of a newbie to macro's..can you tell) query, but essentially you’ve hit on the problem. What I’m ideally looking for is to provide the user with the ability to insert a formatted & formularised row at the bottom of the subsections that were defined in the jpg I’ve posted.

So based on the jpg example, on this particular worksheet, the macro would need to do the following.

* If the button on row 38 is clicked, a new row would be inserted in row 38, moving the subtotal down to row 39.
* If the button on row 50 is clicked, a new row would be inserted in row 50, moving the subtotal down to row 51.
* If the button on row 68 is clicked, a new row would be inserted in row 68, moving the subtotal down to row 69.

.....etc etc.

The problem is that the sub-totals on the other 49 sheets will most probably not be located on rows 38, 50, 68, so I’m looking to import a generic formatted & formularised row, (maybe the row could be contained on a complete separate sheet ?) that all of these buttons would be able to use, as the row to be inserted into the worksheet.

I understand that the macro you have provided will only works on the active sheet...but how would the individual buttons on these 50 sheets know where to insert the row ?

Would the code you've provided cater for the above flexibility & if so is the use of a complete separate sheet be a wise choice ?

Also..I like the idea of the Form toolbar button thingy...but ive got no idea how to set that up....could you point me in the right direction ?

Thanks in advance...you've been a real help !!!!!!

Regards

Valenuk1

 

[valenuk1]

Hi there...just thought id give an update.

Ive inserted the code provided into the macro...but im having trouble getting the rows to be inserted into the correct places ?

Can someone point me in the right direction ?

Thanks

Valenuk1

 

[HalfAce]

Hello valenuk1,
See if this is what you're after. If so you should be able to have all your buttons call the same sub.

Sub InsertRowHere()
Dim Btn As Shape
Set Btn = ActiveSheet.Shapes(Application.Caller)
Rows(Btn.TopLeftCell.Row).EntireRow.Insert
End Sub

If this is what you want, and you're using formulas to give you the sub totals of each "section" of data (as it would seem by looking at it) then be sure to use relative cell references instead of absolute references in the sub total formulas so they'll update to include the rows as they're added.

Hope it helps.
Dan

 

[Guentjo]

This is crude, but it might get you started.

Sub Insert_Subtotals()
'

For i = 15 To 65536
    
Range("E" & i).Select
If ActiveCell.Offset(2, 0).Value = "Risks" Then
ActiveCell.Offset(0, 2).FormulaR1C1 = "Sub Totals"
ActiveCell.Offset(0, 5).FormulaR1C1 = "=SUBTOTAL(9,R11C:R" & i - 1 & "C)"
Else
    
    Range("E" & i).Select
    If ActiveCell.Offset(1, 0).Value = "Other" Then
    ActiveCell.Offset(0, 2).FormulaR1C1 = "Sub Totals"
    ActiveCell.Offset(0, 5).FormulaR1C1 = "=SUBTOTAL(9,R11C:R" & i - 1 & "C)"
    Else
       
        If ActiveCell.Offset(3, 0).Value = "Adjustments for Best Position" Then
        ActiveCell.Offset(0, 2).FormulaR1C1 = "Sub Totals"
        ActiveCell.Offset(0, 5).FormulaR1C1 = "=SUBTOTAL(9,R11C:R" & i - 1 & "C)"
        Else
            
            If ActiveCell.Offset(2, 0).Value = "Adjustments for Worst Position" Then
            ActiveCell.Offset(0, 2).FormulaR1C1 = "Sub Totals"
            ActiveCell.Offset(0, 5).FormulaR1C1 = "=SUBTOTAL(9,R11C:R" & i - 1 & "C)"
            Exit Sub
            Else
            End If
        End If
    End If
End If

Next i

'
End Sub