?
Solved

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

Posted on 2015-01-25
13
Medium Priority
?
142 Views
Last Modified: 2015-01-26
I have a workbook with several worksheets in it.
“Checksheets-ALL”
“MC Tracker Data”
“Orbit Dump”
“Tracking”
“Mapping”

“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?
Check-Sheet-Log-Template-V03.xlsm
0
Comment
Question by:DougDodge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40569742
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.
0
 

Author Comment

by:DougDodge
ID: 40569803
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.
0
 

Author Comment

by:DougDodge
ID: 40569951
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.
0
Supports up to 4K resolution!

The VS192 2-Port 4K DisplayPort Splitter is perfect for anyone who needs to send one source of DisplayPort high definition video to two or four DisplayPort displays. The VS192 can split and also expand DisplayPort audio/video signal on two or four DisplayPort monitors.

 
LVL 18

Expert Comment

by:Simon
ID: 40570085
>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.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40570090
Question
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.

gowflow
0
 

Author Comment

by:DougDodge
ID: 40570184
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"
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40570319
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 ?
gowflow
0
 

Author Comment

by:DougDodge
ID: 40570527
New Button Please
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40570708
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
SyncMCTrackingToCheckSheets

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
WSMC.Range("C:C").EntireColumn.Cut
WSMC.Range("A:A").EntireColumn.Insert

'---> 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
        Else
            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
WSMC.Range("A:A").EntireColumn.Cut
WSMC.Range("D:D").EntireColumn.Insert

'---> 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


NOTE:
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.

gowflow
Check-Sheet-Log-Template-V04.xlsm
0
 

Author Comment

by:DougDodge
ID: 40570958
Greetings,

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.
Check-Sheet-Log-Template-V05.xlsm
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 40571012
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.
gowlfow
0
 

Author Closing Comment

by:DougDodge
ID: 40571172
My mistake, I applied a filter wrong. Sorry......
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40572178
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.

gowflow
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question