Solved

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

Posted on 2015-01-25
13
134 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
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Array formula Help 11 62
Calculating Standard Deviation inside Excel. 5 106
How do "runtime modules" work in Excel? 6 50
what is the best open source database solution? 3 41
Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

856 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