Solved Adding/Removing Rows in Source Sheets Auto Changes Master

January 2, 2019 at 16:43:28
Specs: Windows 10
I'm in need of a bit of coding help with our inventory document. I'm sure my question would be easily solved with a dedicated inventory program, but management insists on an Excel form.

We have a small Excel file with 7 sheets of data; each a different item category. We edit each sheet monthly and print them individually without issue. I was asked to merge all 7 data sheets into 1 master sheet for easier viewing and printing, which is simple enough to do but I'd like to automate the process. A few people access this file and edit the sheets monthly, so I'd prefer to keep the "Master List" sheet protected. Allow it to simply pull data from the editable source sheets and be used solely as a printable master sheet.

I mostly found the solution I was looking for here: https://www.computing.net/answers/o...

My issue with the code provided in the link above is that it doesn't automatically update. The code only works if I run it after everything in the source sheets have already been edited.

Is there a way to get a similar result without having to run the module every time? I would have to train multiple people and new employees on this and eventually someone will forget to run the module and update the master list.

Thank you all in advance for your help.


See More: Adding/Removing Rows in Source Sheets Auto Changes Master

Report •

✔ Best Answer
January 4, 2019 at 17:28:44
re: When the information is sent to the "Master" sheet, can I have it create specific new cells with certain text/formulas?

Sure, you could hard code the formula or you could let VBA build the formula. The possible methods are almost endless.

Hard Code:

Sheets("Master").Range("A15").Formula = "=SUM(A1:A14)"

One of many ways to build a formula:

Sub BuildFormula()
'Determine last row with data in Column A
  lastRw = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
  
'Place SUM formula in next empty sell
  Sheets("Master").Range("A" & lastRw + 1).Formula = "=SUM(A1:A" & lastRw & ")"

End Sub

re: Can I set the "Master" sheet to fill a cell a red color if the value is equal to 0?

Why not just use conditional formatting?


message edited by DerbyDad03



#1
January 3, 2019 at 06:12:57
There are multiple ways to have the code run automatically, but it depends on when you want it to run. You know your users and work processes better than we do, so you would need to tell us when you want the code to run. However, I do want to point out one issue with "automatization":

Macros cannot easily be undone. The Undo button will not work for changes made by a macro. The only way to undo changes made by a macro is with another macro and that gets really, really complicated. The "undo code" would need to know exactly what the workbook looked like prior to the changes made by the main macro. Trust me, it's easier to live by the rule that macros can not be undone than to consider writing "undo code".

The reason I bring that up is this: "users"

Users make mistakes, users change their minds. If the code is automated based on user actions, then you run the risk of a single keystroke blowing up your workbook. Only you know the risks based on your work processes.

That said, the code could be set up to automatically run whenever a user makes a change, such as within a specific column or based on specific value, etc. It could also run automatically as soon as the user clicks the Save or Save As button or the Close button or activates/deactivates a specific sheet(s). etc.

There are over 100 Application, Workbook, Worksheet and Chart events. Tell us what action you want to use to trigger the code and we'll see what we can do.

Here is an excellent write-up on Events in VBA:

http://www.cpearson.com/excel/Event...

message edited by DerbyDad03


Report •

#2
January 3, 2019 at 06:56:32
It would be best to have the code run when saving, and when accessing the "Master" sheet. The master is primarily used as a page to print from, so having it run when selecting the page to print would be helpful.

Every source sheet will be edited by multiple users. I understand the risks here as well but changing this isnt my call to make.

Also one more request to make my life a little easier. The code you provided in your response on the linked page looks for the last populated A column cell to pull information. That is wanted still. However, I would also be interested in having it look for the last B column cell to pull from. Every B cell is populated but some A cells are not; so if by chance the A is not populated for the last listed item, it would not be used. What part of the code will need to change to specific the B column?

Thank you so much for your help.


Report •

#3
January 3, 2019 at 08:27:48
I'm a bit confused by your "Last Cell" question.

You say that you "still want" to check Column A but then you say "Every B cell is populated but some A cells are not". Why do you "still want" to check Column A if Column B is the better choice?

If you want the last cell determined by Column B, change the "1" in this instruction to "2".

lastRow = Sheets(shtNum).Cells(Rows.Count, 1).End(xlUp).Row

The "1" is the ColumnIndex argument for the Cells method. Cells(RowIndex, ColumnIndex)

As examples, you can use either a number, a string, a variable or a constant for the arguments. All of the following constructs reference B1.

Cells(1, 2)

Cells(1, "B")

myCol = "B"
  Cells(1, myCol)

myCol_Sum = 1 + 1
myRw_Sum = 0 + 1
  Cells(myRw_Sum, myCol_Sum)

As far as automating your code, either one of the following syntaxes should work. I don't have your workbook to test against, so I'm simply providing some generic code examples.

In the VBA editor, click on the ThisWorkbook module. Paste the following code into the pane that opens. These examples are just to show you how the BeforeSave and the SheetActivate events could be used. Activate the Master sheet and/or Save the workbook to trigger the events.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'---> Your code would go here <---

'Example code follows
   MsgBox "This Workbook Is About To Be Saved"
   
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'---> Your code would go here <---

'Example code follows
   If ActiveSheet.Name = "Master" Then
     MsgBox "I Am Your Master"
   End If

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
January 3, 2019 at 09:35:35
My "last cell question" was referring to a request to see coding for both column A and column B so that I could learn the difference. You've done a fine job of explaining that though already. Sorry for the confusion there.

For automation: I've copied the code as instructed and the process works perfectly. Thank you again. You've been extremely helpful.


Report •

#5
January 3, 2019 at 09:41:42
I'm glad it worked and apparently worked so easily.

There are often times when code needs some modification to run in the ThisWorkbook module or to be used as an Event driven macro. Let me know if any issues pop up.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#6
January 4, 2019 at 12:47:06
DerbyDad3,

I have two requests to add to the "Master" page and have the workbook do them automatically. I figured I'd post it here since it is in regards to the same document; I apologize if this should be a new post given the new questions.

1: One of the columns that I have in each sheet has a final cost for each item row. Each sheet has its own cell at the bottom that adds up a total cost for every row in that sheet. When the information is sent to the "Master" sheet, can I have it create specific new cells with certain text/formulas? The purpose of this would be to create a "Total Cost" amongst all sheets cell.

2: Can I set the "Master" sheet to fill a cell a red color if the value is equal to 0? This can be done on each source sheet and just have the information carry over, but I was requested to keep the source sheets without color.

Thank you for your continued assistance.


Report •

#7
January 4, 2019 at 17:28:44
✔ Best Answer
re: When the information is sent to the "Master" sheet, can I have it create specific new cells with certain text/formulas?

Sure, you could hard code the formula or you could let VBA build the formula. The possible methods are almost endless.

Hard Code:

Sheets("Master").Range("A15").Formula = "=SUM(A1:A14)"

One of many ways to build a formula:

Sub BuildFormula()
'Determine last row with data in Column A
  lastRw = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
  
'Place SUM formula in next empty sell
  Sheets("Master").Range("A" & lastRw + 1).Formula = "=SUM(A1:A" & lastRw & ")"

End Sub

re: Can I set the "Master" sheet to fill a cell a red color if the value is equal to 0?

Why not just use conditional formatting?


message edited by DerbyDad03


Report •

Ask Question