Solved

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

Posted on 2015-01-25
13
122 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
  • 6
  • 5
  • 2
13 Comments
 
LVL 18

Expert Comment

by:SimonAdept
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
 
LVL 18

Expert Comment

by:SimonAdept
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 29

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 29

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 29

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 29

Accepted Solution

by:
gowflow earned 500 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 29

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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now