VBA Help

[Simple_man916]

Hello:

I am new to VBA and could really use some VBA Help.

I am trying to Auto update data entered on a "Tracker" sheet and "Archive" sheet to a "Master" sheet without use of a button. (Real time data transfer)

The worksheet I am creating combines data from my "Tracker" sheet and "Archive" to a master sheet to run reports utilizing a pivot sheet and chart sheet. My current codes manipulates my "Master sheet" by inserting new columns, adding borders, highlights every other row throughout whole "Master" Sheet.



Two questions:
1. How do I use change_event code to update "Master" sheet with any changes made to made to My "tracker" and "Archive" sheet and where do I place code?

2. Can someone review my codes? I think there is a easier way rather than running 9 Modules.

Below is my code used in 9 modules.

Sub Combine()
Dim J As Integer
Dim wrk As Workbook
On Error Resume Next

Set wrk = ActiveWorkbook

Application.ScreenUpdating = False


Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Master"

Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")


For J = 2 To Sheets.Count - 1
Sheets(J).Activate
Range("A1").Select



Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)


Application.ScreenUpdating = True
Next
End Sub

Sub InsertColumns()

'PURPOSE: Insert column(s) into the active worksheet
'SOURCE: Squarespace - Claim This Domain

'Insert Column to the left of Column D
Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow

'Insert 2 Columns to the left of Column G
Columns("G:j").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Cells(1, 1) = "Case Number"
ActiveSheet.Range("1:1").Font.Bold = True
Range("A1").Interior.Color = RGB(217, 217, 217)
Cells(1, 7) = "Month Number"
Cells(1, 8) = "Month"
Cells(1, 9) = "Day"
Cells(1, 10) = "Year"

End Sub

Sub Borders()


Range("a2:Z2").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(141, 180, 226)
End With

Range("Z2").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(141, 180, 226)
End With

Range("a1").Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(0, 0, 0)
End With

End Sub

Sub ShadeEverySecondRow()
Dim LastRow As Long
Dim i As Long
With ThisWorkbook.ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlDown).Row + 1
For i = 2 To LastRow Step 2
.Cells(i, 1).Resize(, 26).Interior.Color = RGB(220, 230, 241)
Next i
End With
End Sub

Public Sub example()

'r = row, c = column
Application.Goto Reference:="R1C2"
Selection.End(xlDown).Select

Dim maxRowIndex As Integer
'-1 to account for the header row. Remove or adjust at will
maxRowIndex = ActiveCell.Row - 1

'set up starting point of repetition structure
Range("a2").Select
Dim rowCounter As Integer
rowCounter = 1

'begin populating sequence
For rowCounter = 1 To maxRowIndex
'populate number in sequence
ActiveCell = rowCounter
'go to next row
ActiveCell.Offset(1).Select
Next


End Sub

Sub CopyDate()

Range(Range("b2"), Range("b2").End(xlDown)).Copy Range("g2")
Range(Range("b2"), Range("b2").End(xlDown)).Copy Range("h2")
Range(Range("b2"), Range("b2").End(xlDown)).Copy Range("i2")
Range(Range("b2"), Range("b2").End(xlDown)).Copy Range("j2")

End Sub

Sub DateExtract()
Range(Range("g2"), Range("g2").End(xlDown)).Select
Selection.NumberFormat = "MM"

Range(Range("H2"), Range("H2").End(xlDown)).Select
Selection.NumberFormat = "MMm"

Range(Range("I2"), Range("I2").End(xlDown)).Select
Selection.NumberFormat = "dd"

Range(Range("J2"), Range("J2").End(xlDown)).Select
Selection.NumberFormat = "yyyy"

Worksheets("Master").Columns("A:z").AutoFit
End Sub

Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:z50000").Address(ReferenceStyle:=xlR1C1)

'Create a new worksheet
Set sht = Sheets.Add
sht.Name = "Pivot"

'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="Pivot My Tracker")

End Sub

Sub createPivotChart1a()


Dim PvtTbl As PivotTable
Dim rngChart As Range
Dim objChart As Chart
Set PvtTbl = Worksheets("Pivot").PivotTables("Pivot My Tracker")

'use the Charts.Add Method to add a new chart sheet in the workbook, which is represented as a Chart object

'adds new chart sheet before or after specified sheet:
ActiveWorkbook.Charts.Add Before:=Worksheets("Pivot"), Count:=1

'Alternate 1: to add new chart sheet before the active sheet, use below code line:
'Set objChart = Charts.Add
'Alternate 2: to add new chart sheet after the last worksheet, use below code line:
'ActiveWorkbook.Charts.Add After:=Worksheets(Worksheets.Count)

'set as active worksheet:
Set objChart = ActiveSheet

'set range for the PivotChart, based on the existing PivotTable:
Set rngChart = PvtTbl.TableRange2

'specify the source data for the PivotChart, name of chart sheet and the chart type:
With objChart

.SetSourceData rngChart
.Name = "Chart"
.ChartType = xlColumnClustered

End With


End Sub


Thank you in advance any help would be greatly appreciated.