Newbie: Vba auto populate data from two refrence sheets to Master with different Ranges

[Simple_man916]

Hello:

I am new to VBA and Looking for code to link data from two reference sheets to a master sheet with different columns ranges.

Two reference sheets "Tracker" & " Archive" have columns A:U first row headers
Master sheet columns A:Z first row headers

Need code to Link columns A:E from reference sheets to B:F on Master sheet
Need to Link columns F:U from reference sheets to K:Z on Master sheet

Unable to get similar codes to work and auto populate on Master sheet when reference sheets are changed

Can some one help?

Thank you

I uploaded a sample of the worksheet I created. My master sheet has added columns to run reports.

I think this is why my change event code don't work.

Below is a lengthy code I created running through 9 modules (I know Crazy) I am new

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

'We don't want screen updating
Application.Scr eenUpdating = 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()

    Columns("A:A").Insert Shift:=xlToRight, _
      CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
    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

Public Sub NumberColA()
   
    '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()
Copies received date column b to column g to j

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()

formats dates g to j

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

Trying to auto-populate data on master when reference sheets have changed to run reports

 

[vletm]

Interesting ...
but could You add some sample data and
wanted result from that data somewhere?
I would be less challenge to get better image of You needs.