How do I use VBA to update a worksheet from another worksheet?

I have a workbook with several worksheets in it.
“MC Tracker Data”
“Orbit Dump”

“Checksheets-ALL” is the control worksheet, and working document.
“MC Tracking Data” is used to normalize “Tracking” into a much more user friendly state.
“Tracking” & “Orbit Dump” get deleted and replaced daily.
“Mapping” is just a reference as to the “Checksheets-All” columns and where the data should come from.
In addition, “Checksheets-ALL” Row 6 also show the mapping and “MC Tracking Data” Row 3 also shows the mapping.

Currently the Module “modDailyUpdate” and the Sub “DailyUpdate()” align “Checksheets-ALL” and “Orbit Dump” by use of Column 1 in both with a Unique Identifier which is just a concatenation of Col D & E in “Checksheets-ALL” As well as Col D & I in “Orbit Dump”

The help I am looking for is using Col F in “Checksheets-ALL” matching to Col C in “MC Tracker Data” how do I populate the remaining columns in “Checksheets-ALL” with data from “MC Tracker Data” using VBA?
Who is Participating?
gowflowConnect With a Mentor Commented:
How did you conclude that it did not take into consideration V and W ?
Did you run the new macro ?

You should press on the button Sync MC Traker button and once done check the results. It has nothing to do with Daily Update button.

Let me know.
If you were doing it manually, would it be a series of vlookups?

Your code is already doing something similar (in Sub DailyUpdate) to fill columns with data from Orbital Dump. Wouldn't you just adapt that code?

I wasn't quite clear which columns you wanted to populate. Is it columns N-W on the Checksheets-ALL sheet, with data from MC Tracker Data "Y"-"AF" and "BJ" and "BM"?

I'd suggest you keep a hidden row on Checksheets-ALL with the relevant vlookup formulae and use VBA to copy these down the used range as required and convert to values.
DougDodgeAuthor Commented:
The issue as I see it with using VLOOKUP is that it might need to use dynamic named ranges. The column in "Checksheets-ALL" stay static, as do the column in "Orbit Dump" and "MC Tracker Data" but the rows numbers bounce up and down daily.
Naming the ranges and using VBA to insert and do a fill down is one approach that would work. Would I not have to ascending sorting multiple times during that process?
"Checksheets-ALL" is the control, it always stays in the workbook, it needs the updates from "MC Tracker Data". The mapping is in Row 6 of Checksheets-ALL" relating the Col number from "MC Tracker Data" that holds the source.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

DougDodgeAuthor Commented:
I would probably use VLOOKUP, you might have a good point. Maybe just have VBA do a fill down of the formula might be the fastest.
>The issue as I see it with using VLOOKUP is that it might need to use dynamic named ranges.
I usually use entire column references for VLOOKUP where possible
e.g. =VLOOKUP(M9,'MC Tracker Data'!C:BB,23,FALSE)
 Excel is pretty smart at working out which of the cells have content in and need to be searched for matches.

>Would I not have to ascending sorting multiple times during that process?
You don't need a sorted range for vlookup.
Is the update required of records in “MC Tracker Data” to CheckSheets-All comes as part of Daily Update or it is a total new one that is not related ? In other words we need to locate the 'New records' produced (existing in Orbit Dump but not in CheckSheets-All) and now affect for these records the corresponding fields from “MC Tracker Data” ?

In other words I need to know the extent of this new macro should it be run on ALL the records of “MC Tracker Data” to find their corresponding in CheckSheets-All and update them or just a part and if a part then what is that part.

DougDodgeAuthor Commented:
Since the records in "Tracking" that is the source for "MC Tracker Data" is changing constantly that makes the records of "MC Tracker Data" change all the time, all records of "MC Tracker Data" require to be matched to their corresponding record in "Checksheets-ALL"
ok great then shall we create a new button for this update of you want to link it to an existing button ? if latter then which one ?
DougDodgeAuthor Commented:
New Button Please
ok you did not advise where to link the macro I created a new button called Sync With MC Tracker and plugged the below code that you will find in the modDailyUpdate Module called

Sub SyncMCTrackingToCheckSheets()
Dim WSC As Worksheet
Dim WSMC As Worksheet
Dim WSM As Worksheet
Dim MaxRowC As Long, MaxRowMC As Long, MaxRowM As Long, I As Long
Dim MinRowC As Long, MinRowMC As Long
Dim sColMC As String, sColC As String
Dim lColMC As Long

'---> Set Variables
Set WSC = Sheets("Checksheets-ALL")
MaxRowC = WSC.Range("A" & WSC.Rows.Count).End(xlUp).Row
MinRowC = 9
Set WSMC = Sheets("MC Tracker Data")
MaxRowMC = WSMC.Range("A" & WSMC.Rows.Count).End(xlUp).Row
MinRowMC = 6
Set WSM = Sheets("Mapping")
MaxRowM = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

'---> Sort CheckSheets-All based on Col F
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC).Sort Key1:=WSC.Range("F" & MinRowC), order1:=xlAscending, Header:=xlNo
'---> Sort CheckSheets-All based on Col C
WSMC.Range("A" & MinRowMC & ":ZZ" & MaxRowMC).Sort Key1:=WSMC.Range("C" & MinRowMC), order1:=xlAscending, Header:=xlNo

'---> Transfer Col C in MC Traker Data to A

'---> Create the remaining fields as per Mapping in Checksheets-ALL for
'     All records of MC Traker Data.
For I = 2 To MaxRowM
    sColMC = Trim(WSM.Cells(I, "C"))
    If sColMC <> "" Then
        If WSC.Columns(sColMC).Column < WSC.Columns("C").Column Then
            lColMC = Abs(WSC.Columns(sColMC).Column) + 1
            lColMC = Abs(WSC.Columns(sColMC).Column)
        End If
        sColC = Trim(WSM.Cells(I, "A"))
        sColMC = "BU"
        '---> Create a VLOOKUP formula in CheckSheets-All to pull coresponding data from MC Tracker Data
        '     then once created copy/pastepecial this fomula as values to reduce calculation times.
        '     =IFERROR(IF(OR(ISERROR(VLOOKUP(F9,'MC Tracker Data'!$C$6:$Y$201,23,0)),VLOOKUP(F9,'MC Tracker Data'!$C$6:$Y$201,23,0)=""),"",VLOOKUP(F9,'MC Tracker Data'!$C$6:$Y$201,23,0)),"")
            'WSC.Range(sColC & MinRowC & ":" & sColC & MaxRowC).Formula = "=IFERROR(IF(OR(ISERROR(VLOOKUP(F" & MinRowC & ",'MC Tracker Data'!$C$" & MinRowMC & ":$" & sColMC & "$" & MaxRowMC & "," & lColMC & ",0)),VLOOKUP(F" & MinRowC & ",'MC Tracker Data'!$C$" & MinRowMC & ":$" & sColMC & "$" & MaxRowMC & "," & lColMC & ",0)=""""),"""",VLOOKUP(F" & MinRowC & ",'MC Tracker Data'!$C$" & MinRowMC & ":$" & sColMC & "$" & MaxRowMC & "," & lColMC & ",0)),"""")"
            WSC.Range(sColC & MinRowC & ":" & sColC & MaxRowC).Formula = "=IFERROR(IF(OR(ISERROR(VLOOKUP(F" & MinRowC & ",'MC Tracker Data'!$A$" & MinRowMC & ":$" & sColMC & "$" & MaxRowMC & "," & lColMC & ",0)),VLOOKUP(F" & MinRowC & ",'MC Tracker Data'!$A$" & MinRowMC & ":$" & sColMC & "$" & MaxRowMC & "," & lColMC & ",0)=""""),"""",VLOOKUP(F" & MinRowC & ",'MC Tracker Data'!$A$" & MinRowMC & ":$" & sColMC & "$" & MaxRowMC & "," & lColMC & ",0)),"""")"
            WSC.Range(sColC & MinRowC & ":" & sColC & MaxRowC).Copy
            WSC.Range(sColC & MinRowC).PasteSpecial xlPasteValues
    End If
Next I

'---> Sort CheckSheets-All based on Col A
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC).Sort Key1:=WSC.Range("A" & MinRowC), order1:=xlAscending, Header:=xlNo
'---> Transfer back Col A in MC Traker Data to C

'---> Ensable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With

MsgBox "MC Tracking Update Done !", vbExclamation

End Sub

Open in new window

It seems that you forgot to add in sheet Mapping the mapping for Col V, W to be respectively BJ,BM I proceeded to add them if you have any reason why they should not please remove them from the Sheet Mapping they are in red.

DougDodgeAuthor Commented:

You are correct regarding the mapping. The attached file shows a filter on both "Checksheets-ALL" and "MC Tracker Data".
It seems to not populate columns V & W from "MC Tracker" column BJ & BM.
DougDodgeAuthor Commented:
My mistake, I applied a filter wrong. Sorry......
ok no problem I will not monitor this question if you need further help pls post in the normalization question that I will keep monitoring.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.