New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Macro to delete/add same row on multiple sheets

Macros, VBA, Excel Automation, etc.

Macro to delete/add same row on multiple sheets

Postby tancho321 » Tue Jan 10, 2012 4:30 pm

Hi All,
I am trying to put together a macro that enables me to select any cell in column C and then delete that row in that and a second worksheet, I can get a macro to delete a given row on sheet 1 but can't get it to perform the same deletion on sheet 2 automatically. The code I have used is (apologies if I get the layout wrong but am fairly new to VBA :oops: ):

Sub Delete()

Sheet1.Unprotect Password:="password"

Selection.EntireRow.Delete

Sheet1.Protect Password:="password"

End Sub

I also need to be able to add rows in the same way and to include any of the formula from cells in the row above it. Is this possible? Apologies if this is a very straightforward problem but any help would be gratefully received.
Many thanks
Tancho
  • 0

tancho321
Rookie
 
Posts: 6
Joined: Jan 10, 2012
Reputation: 0

Re: Macro to delete/add same row on multiple sheets

Postby Sisyphus » Wed Jan 11, 2012 3:58 am

Hi,
Do you have password protection on your two sheets? Or on the workbook? Or are these sheets protected without password? I hope to learn that workbook and worksheets are "just normal", but if they are protected you should state the precise kind of protection used.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Macro to delete/add same row on multiple sheets

Postby Don » Wed Jan 11, 2012 4:22 pm

Are you only going to be selecting the cell/row before you run the macro or do you need that programmed into the macro? If you could include a sample worksheet that illustrates exactly what you need to do, it would be much easier to help you with the vba.
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: Macro to delete/add same row on multiple sheets

Postby tancho321 » Wed Jan 11, 2012 4:55 pm

Hi Both,
Thank you for looking at this for me. Each sheet is password protected separately but with the same password.

I have uploaded a test file. The password on each sheet is "password". Basically what I need is a macro that when I select a cell with a name it deletes that entire row on sheet 1 but also the same on sheet 2 when you click a button. Sheet 2 is slightly different to Sheet 1.

I then need the ability to add a row below the names but be able to have the same formula as the other rows in the same way via a button. I am after an "Add Row" and "Delete Row" button with the relevant macro assigned if that makes sense?
Many thanks for your time.
Tancho
  • 0

You do not have the required permissions to view the files attached to this post.
tancho321
Rookie
 
Posts: 6
Joined: Jan 10, 2012
Reputation: 0

Re: Macro to delete/add same row on multiple sheets

Postby Sisyphus » Thu Jan 12, 2012 5:12 am

Hi,
At the heart of the solution you asked for is this code, contained in a normal code module:
Code: Select all
Option Explicit
Option Base 0

    Const PW As String = "Password"
   
    Public Enum Act
        ActAdd = 1
        ActDel
    End Enum

Public Sub AddOrDeleteRow(ByVal ActWhat As Long)
   
    Dim Ws() As Variant
    Dim R As Long
    Dim i As Integer
   
    Ws = Array("Sheet1", "Sheet2")
    With Selection
        If .Rows.Count > 1 Then Exit Sub
        R = .Row
    End With
    For i = LBound(Ws) To UBound(Ws)
        With Worksheets(Ws(i))
            .Unprotect PW
            If ActWhat = ActDel Then
                .Rows(R).Delete
            Else
                .Rows(R).Insert
            End If
            .Protect Password:=PW, UserInterfaceOnly:=True
        End With
    Next i
End Sub


At the top of the module you will find your password. I have put it there so that you can put it somewhere else. It shouldn't be in the code.
Below that you find a Public enum, assigning the value 1 to ActAdd and 2 to ActDel. 2 isn't declared because the next enum always has the next number.
This enum is used to communicate with the buttons. In the code sheet of Sheet1 you have the following code:
Code: Select all
Option Explicit

Private Sub CmdAdd_Click()
    AddOrDeleteRow ActAdd
End Sub

Private Sub CmdDelete_Click()
    AddOrDeleteRow ActDel
End Sub

Both buttons on the worksheet call the same routine but with a different argument. The Add Row button will send an ActAdd (=1), the Delete Row button an ActDel (=2), so that the AddDeleteRow procedure knows what to do.

This procedure, first of all checks if your instruction is clear. If you have selected more than one row it will do nothing. But if you have selected only one row - never mind, how many cells in it - that row will either be deleted or a row inserted in its place, depending upon which button you pressed.
Code: Select all
    Ws = Array("Sheet1", "Sheet2")

This array decides on which worksheets the action will be carried out. If a worksheet by the name mentioned in this array doesn't exist the macro will crash. If you add a third, fourth etc. sheet to it the same action will be arried out on all of them. No need to make any other change anywhere else.

Code: Select all
  .Protect Password:=PW, UserInterfaceOnly:=True

When you set the protection using VBA you have a property at your disposal which allows you to access using code the worksheet without the need for unprotecting it while the user must still enter a password to do the same thing. This is the UserInterfaceOnly property which the above code sets to True. So, on second use unprotecting the sheets will not be necessary, and should you decide to run other code on them, don't be surprised if you can do so without lifting the protection. The reason is right here.
  • 0

You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Macro to delete/add same row on multiple sheets

Postby tancho321 » Sun Jan 15, 2012 10:34 am

Hi
Thank you for your explanation and code. The Add/Delete function works perfectly and just as I need but when I add a row the formula that is in the other rows appears to be missing. I have attached a simplified updated example where the user inputs data into Columns B & C. Column D then has a formula that adds the 2 together. When I add a row the formula doesn't appear to work in the new row. Am I doing something wrong?

Many thanks for your help with this problem. Your time is appreciated.
  • 0

You do not have the required permissions to view the files attached to this post.
tancho321
Rookie
 
Posts: 6
Joined: Jan 10, 2012
Reputation: 0

Re: Macro to delete/add same row on multiple sheets

Postby Sisyphus » Sun Jan 15, 2012 7:24 pm

Hi,
I couldn't find proof of what you say in your workbook. Perhaps you can better point out where and how the error occurs.
Of course, if you delete a cell referenced by a formula that formula can't produce the correct result any more. I saw that your Sheet2 takes all its values from Sheet1. After you delete Sheet1, which the code is doing first, Sheet2 can't find the values it is supposed to show. This state should end when the row having all the errors is deleted a fraction of a second later. However, you can easily detrmine the sequence of deletion by modifying this line in the code:
Code: Select all
    Ws = Array("Sheet1", "Sheet2")

For your workbook it would be better to delete Sheet2 first, followed by Sheet1. The macro works down the list left to right.

Not that that should make any difference I can presently see. It's just more tidy. The error you point out should be caused by vertical sums, but I didn't find any such formulae in your workbook.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Macro to delete/add same row on multiple sheets

Postby tancho321 » Tue Mar 06, 2012 5:02 pm

Hi Sisyphus,
I need to apologise. I thought I had posted to thank you and to say that your solution did work. I have just seen that I didn't do that. Many apologies but thank you for your help.
Regards
Tancho
  • 0

tancho321
Rookie
 
Posts: 6
Joined: Jan 10, 2012
Reputation: 0

Re: Macro to delete/add same row on multiple sheets

Postby mamunges » Mon Aug 15, 2016 3:24 am

Hi Sisyphus,

I used the above code which doing great. But now I have a problem on the same aspect where add and remove micro work in unprotected sheets. In where of the code you made I need to change to do the job done.
Code: Select all
Option Explicit
Option Base 0

    Const PW As String = "Password"
   
    Public Enum Act
        ActAdd = 1
        ActDel
    End Enum

Public Sub AddOrDeleteRow(ByVal ActWhat As Long)
   
    Dim Ws() As Variant
    Dim R As Long
    Dim i As Integer
   
    Ws = Array("Sheet1", "Sheet2")
    With Selection
        If .Rows.Count > 1 Then Exit Sub
        R = .Row
    End With
    For i = LBound(Ws) To UBound(Ws)
        With Worksheets(Ws(i))
            .Unprotect PW
            If ActWhat = ActDel Then
                .Rows(R).Delete
            Else
                .Rows(R).Insert
            End If
            .Protect Password:=PW, UserInterfaceOnly:=True
        End With
    Next i
End Sub

Regards
  • 0

mamunges
Rookie
 
Posts: 10
Joined: Apr 18, 2016
Reputation: 0
Excel Version: 2007


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 7 guests