Macros VBA update data automatically

Stacey16

New Member
Joined
Feb 3, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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



VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,151
Office Version
  1. 2016
Platform
  1. Windows
You could use VBA icon there to paste your code
VBA 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

 

Watch MrExcel Video

Forum statistics

Threads
1,129,529
Messages
5,636,853
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top