Simple_man916
New Member
- Joined
- Aug 22, 2016
- Messages
- 10
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.
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.
Last edited by a moderator: