Solved

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

Posted on 2015-01-25
13
130 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: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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

803 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