Macros VBA update data automatically

[Stacey16]

Im wondering if you can help me.



I followed a tutorial for Macros VBA on how to create additional Tabs for months.

Which worked great, but I was wondering if you can tell me how can you make all the month tabs update automatically when I edit the Master tab.



This was the tutorial I did,

Excel Worksheet Macros



with this coding

Option Explicit



Sub AddMultiMonthWksts()

Dim wsA As Worksheet

Dim wsM As Worksheet

Dim wsMth As Worksheet

Dim bCheck As Boolean

Dim wsYr As Long

Dim strCell As String

Dim strTitle As String

Dim strTabs As String

Dim wsTitle As String

Dim wsTab As String

Dim wsStart As Long

Dim wsEnd As Long

Dim wsSht As Long

Dim shDate As Date

Dim strMthT As String

Dim strMthTb As String

Dim strYtT As String

Dim strYrTb As String

Dim strMsg As String



On Error Resume Next

Set wsA = Sheets("Wkst_Admin")

Set wsM = Sheets("Wkst_Master")

strMsg = "Could not add all sheets"



On Error Resume Next

With wsA

wsYr = .Range("wsYr").Value

If wsYr = 0 Then

strMsg = "Please enter year number" _

& vbCrLf _

& "and try again."

GoTo errHandler

End If

strCell = .Range("wsCell").Value

strTitle = .Range("wsTitle").Value

strTabs = .Range("wsTabs").Value

wsStart = .Range("wsStart").Value

wsEnd = .Range("wsEnd").Value

If wsStart = 0 Or wsEnd = 0 Then

strMsg = "Please enter month start " _

& "and end numbers" _

& vbCrLf _

& "and try again."

GoTo errHandler

End If

End With

On Error GoTo errHandler



'add sheets before master sheet

For wsSht = wsStart To wsEnd

strMsg = "Could not get date for" _

& vbCrLf _

& "Year " & wsYr _

& " and Month " & wsSht

shDate = DateSerial(wsYr, wsSht, 1)



strMsg = "Could not get sheet name for" _

& vbCrLf _

& "Year " & wsYr _

& " and Month " & wsSht

wsTab = Range("wsTabs").Value

wsTab = Replace(wsTab, "xxxx", _

Format(shDate, "mmmm"))

wsTab = Replace(wsTab, "xxx", _

Format(shDate, "mmm"))

wsTab = Replace(wsTab, "xx", _

Format(shDate, "mmm"))

wsTab = Replace(wsTab, "yyyy", _

Format(shDate, "yyyy"))

wsTab = Replace(wsTab, "yy", _

Format(shDate, "yy"))

On Error Resume Next

bCheck = Len(Sheets(wsTab).Name) > 0

On Error GoTo errHandler

If bCheck = False Then

'add new sheet after Instructions

strMsg = "Could not get add sheet " & wsTab

wsM.Copy Before:=wsM

With ActiveSheet

.Name = wsTab

If strCell <> "" Then

wsTitle = strTitle

wsTitle = Replace(wsTitle, "xxxx", _

Format(shDate, "mmmm"))

wsTitle = Replace(wsTitle, "xxx", _

Format(shDate, "mmm"))

wsTitle = Replace(wsTitle, "xx", _

Format(shDate, "mm"))

wsTitle = Replace(wsTitle, "yyyy", _

Format(shDate, "yyyy"))

wsTitle = Replace(wsTitle, "yy", _

Format(shDate, "yy"))

.Range(strCell).Value = wsTitle

End If

End With

End If



Next wsSht



exitHandler:

Set wsA = Nothing

Set wsM = Nothing

Set wsMth = Nothing

Exit Sub



errHandler:

MsgBox strMsg

Resume exitHandler

End Sub

 

[Zot]

You could use VBA icon there to paste your code

your code here

Back to topic....
You can use worksheet event to trigger to run macro. You can read here on how to do it

VBA Worksheet Change and SelectionChange Events in Excel

In this tutorial, we’ll discuss the Change and ChangeSelection worksheet events. The Worksheet_Change event-handler procedure executes whenever any cell in the worksheet is changed and Worksheet_SelectionChange event-handler procedure executes when the selection on the worksheet is changed.

www.brainbell.com