Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Course Of The Month
2 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Spreadsheet Updates
Posted on 2015-01-21
I have a workbook with 4 worksheets in it: "Checksheets-ALL", "Orbit Dump", "MC Tracker Data", "Tracking"
I have had an expert help with the development of the "Daily Update" so far. It is based on a "Unique Identifier" Column A in "Checksheets-ALL" & a "Unique Identifier" in Column A of "Orbit Dump" This allows for the code to run and validate matching cells in the two sheets.
The update I require is with additional fields. The mapping is Attached. (Mapping.xlsx)
Additional to the Daily update, "Checksheets-ALL" requires updates from the two other sheets that are imported daily as per the mapping.
I think this is probably a looping exercise, but with 17,000+ rows of data, it would be a long process. Is there an easier and faster way to perform this?
Mapping.xlsx
Check-Sheet-Log-Template.xlsm
I have had an expert help with the development of the "Daily Update" so far. It is based on a "Unique Identifier" Column A in "Checksheets-ALL" & a "Unique Identifier" in Column A of "Orbit Dump" This allows for the code to run and validate matching cells in the two sheets.
The update I require is with additional fields. The mapping is Attached. (Mapping.xlsx)
Additional to the Daily update, "Checksheets-ALL" requires updates from the two other sheets that are imported daily as per the mapping.
I think this is probably a looping exercise, but with 17,000+ rows of data, it would be a long process. Is there an easier and faster way to perform this?
Mapping.xlsx
Check-Sheet-Log-Template.xlsm
[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
13-
10
23 Comments
Active today
Is this related to the question we just solved ?
If yes I see that your file Check-Sheet-Log-Template.x
gowflow
If yes I see that your file Check-Sheet-Log-Template.x
gowflow
Active today
I just saw your Mapping.xlsx file and it is EXACTLY what is needed !!! very good job. Just imagine having just made the macro to simply copy the row from orbit dump to checksheets-all without paying attention that the columns doesn't match how would your data have looked a total mess !!!
:)
side note
I would appreciate, when we work on an issue and if I propose next step and 'you still need my help' to simply let me know when you post a new question by putting a link to that new question in the old question so I can attend otherwhyse there is no way I can tell that you asked a new question and it was just by coincidence that I stumbled on this one.
For sure, if your fedup with my nasty comments then I understand you simply wanting for new help !
gowflow
:)
side note
I would appreciate, when we work on an issue and if I propose next step and 'you still need my help' to simply let me know when you post a new question by putting a link to that new question in the old question so I can attend otherwhyse there is no way I can tell that you asked a new question and it was just by coincidence that I stumbled on this one.
For sure, if your fedup with my nasty comments then I understand you simply wanting for new help !
gowflow
Active today
Re my previous comment about the file size, no worry I opened it and noticed you posted live data which you should refrain from and scrub your data as this is accessible to public and can be mis-used.
I would suggest you request attention to moderators in this question (the triangle just under the question on top) and ask them to remove your file and you prepare a scrubbed one (scrubbed = data modified)
Back to the essence of this question:
Although I may know what you want, reading this question it is not clear what you want. Can you please re-pphrase what is the objective of this question clearly ?
Rgds/gowflow
I would suggest you request attention to moderators in this question (the triangle just under the question on top) and ask them to remove your file and you prepare a scrubbed one (scrubbed = data modified)
Back to the essence of this question:
Although I may know what you want, reading this question it is not clear what you want. Can you please re-pphrase what is the objective of this question clearly ?
Rgds/gowflow
I need to update "Checksheets-ALL on a daily or even more frequent time frame.
"Loop Synchronization" is also part of this same issue....
Sorry, not sure how to link question as yet.
"Loop Synchronization" is also part of this same issue....
Sorry, not sure how to link question as yet.
Active today
Not a problem but you need to tell me exactly what do you want me or someone else to do in this question as 'loop syncronization' is a somewhat vague issue.
Best is for you to tell us how you do what you do manually and then we can automate this.
gowflow
Best is for you to tell us how you do what you do manually and then we can automate this.
gowflow
Active today
BTW I checked your request for attention and what you asked is that question be deleted is this what you want ?? or you want the file attachment to be removed ? it is 2 different issues if the second then you need to click on request attention and specify exactly what you need or else this question will be deleted !
gowflow
gowflow
Requested file be removed and keep the question. You already know what I am looking for, but when I get to work I will repost a cleansed file. Same file, but strip out most data.
Active today
No honestly I do not know what you want as I am confused with the new worksheets added pls understand we don't know your business and you have to spell it out clearly.
I can make your life easier by simply reposting the last questions item number 3)
Is this what you want to achieve in this question ? Item 3) ?
If yes then what we did in item 2) is simply put the new records found in orbit dump not in checksheets-all to put them in checksheets-all updating only Col A and Y
Now you want that routine to be revised to update not only A and Y but to also look at the mapping table and update all fields that are in Orbit Dump to their corresponding fields in checksheets-all for these records.
Is that what you want ???
Kindly confirm or clarify.
Pls note this is a public forum and questions are meant to be directed for each and every person who could run into similar situations. If the questions and answers are not clear then it is not benefiting no one.
Thank you
gowflow
I can make your life easier by simply reposting the last questions item number 3)
2) If the unique identifier exist in sheet Orbit Dump but DOES not exist in sheet Checksheets-All then
Add the item in Col A of sheet Checksheets-All and put today's date in Col Y
3) When point 2) happens we should:
Copy the entire row of the specific Unique identifier into Checksheets-All and not simply the Col A like all columns.
COMMENTS:
if point 3) is correct then what happens here you have different columns in Orbit Dump than in Checksheets-All which one is which ?? You need to provide a clear match col to col.
If it is the case prefer this to be object of a new question as it would entitle a different approach. If not please clarify.
Is this what you want to achieve in this question ? Item 3) ?
If yes then what we did in item 2) is simply put the new records found in orbit dump not in checksheets-all to put them in checksheets-all updating only Col A and Y
Now you want that routine to be revised to update not only A and Y but to also look at the mapping table and update all fields that are in Orbit Dump to their corresponding fields in checksheets-all for these records.
Is that what you want ???
Kindly confirm or clarify.
Pls note this is a public forum and questions are meant to be directed for each and every person who could run into similar situations. If the questions and answers are not clear then it is not benefiting no one.
Thank you
gowflow
Chechsheets-ALL needs to be updated with information from "Orbit", "MC Tracker Data" according to the mapping.
In addition there is another posting for "Loop Synchronization" that has to be updated prior to this update.
Sorry, I never linked it.
In addition there is another posting for "Loop Synchronization" that has to be updated prior to this update.
Sorry, I never linked it.
Active today
Sorry but it is not clear what you want.
What do you mean by:
When this has to be done and pls explain the mechanics.
by giving a specific example on an unique number with the actual values prior to the update and what it should show after the update.
gowflow
What do you mean by:
Chechsheets-ALL needs to be updated with information from "Orbit", "MC Tracker Data" according to the mapping.
When this has to be done and pls explain the mechanics.
by giving a specific example on an unique number with the actual values prior to the update and what it should show after the update.
gowflow
"Checksheets-ALL" is the control worksheet from which all reports are generated.
There are a number of worksheets that go into producing it. "Orbit" is a dump from a DB.
"MC Tracker Data" also feeds into it. But prior to it being used it needs to have "Tracking" update it. The data in "Tracking" needs to be normalized and get rid of the X/X values and split them into 2 columns in "MC Tracker"
It is a long ang ugly process.
"Orbit" and "Tracking" get replaced daily and sometimes more often than that. Hence the idea if VBA rather than formulas.
There are a number of worksheets that go into producing it. "Orbit" is a dump from a DB.
"MC Tracker Data" also feeds into it. But prior to it being used it needs to have "Tracking" update it. The data in "Tracking" needs to be normalized and get rid of the X/X values and split them into 2 columns in "MC Tracker"
It is a long ang ugly process.
"Orbit" and "Tracking" get replaced daily and sometimes more often than that. Hence the idea if VBA rather than formulas.
Active today
ok fine. what do we do in this question ?
"
by giving a specific example on an unique number with the actual values prior to the update and what it should show after the update.
"
gowflow
"
by giving a specific example on an unique number with the actual values prior to the update and what it should show after the update.
"
gowflow
If the Unique Number is no longer in Orbit, it can stay in Checksheets-ALL as well as the actuals, it just needs to show "Removed" or "Deleted" in Column X "Check Sheet Status"
Active today
ok I can see you have 3 buttons up there
Daily Update
Import Orbit Data
Import Tracker
I guess Daily Update is the one we already developed.
Where do you want this new macro to be kicked of ? shall we add a new button or it to be activated after completion of Daily Update with no new button ??
And what I see there is no use of the Mapping so far in this question at this point. Right ??
gowflow
Daily Update
Import Orbit Data
Import Tracker
I guess Daily Update is the one we already developed.
Where do you want this new macro to be kicked of ? shall we add a new button or it to be activated after completion of Daily Update with no new button ??
And what I see there is no use of the Mapping so far in this question at this point. Right ??
gowflow
Active today
ok fine.
I see that you keep every version of code that we produce in your workbook at the end it become cumbersome. My suggestion for your production file is to only keep the last one or else you may soon run into a mess not knowing which is which.
I counted the following that need to be removed from your workbook:
Module 10 - Sub GetData() You can delete this entire module.
modDailyUpdate2 - Sub DailyUpdate2() You can delete this entire module.
modDailyUpdate - Sub DailyUpdate() Delete this Sub and replace it with the following new code:
Pls let me know outcome.
gowflow
I see that you keep every version of code that we produce in your workbook at the end it become cumbersome. My suggestion for your production file is to only keep the last one or else you may soon run into a mess not knowing which is which.
I counted the following that need to be removed from your workbook:
Module 10 - Sub GetData() You can delete this entire module.
modDailyUpdate2 - Sub DailyUpdate2() You can delete this entire module.
modDailyUpdate - Sub DailyUpdate() Delete this Sub and replace it with the following new code:
Sub DailyUpdate()
Dim WSC As Worksheet
Dim WSD As Worksheet
Dim MaxRowC As Long, MaxRowD As Long, I As Long
Dim MinRowC As Long, MinRowD As Long
Dim CRow As Long, DRow As Long
Dim cCell As Range
'---> Disable Events
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
'---> Set Variables
Set WSC = Sheets("Checksheets-ALL")
MaxRowC = WSC.Range("A" & WSC.Rows.Count).End(xlUp).Row
MinRowC = 9
Set WSD = Sheets("Orbit Dump")
MaxRowD = WSD.Range("A" & WSD.Rows.Count).End(xlUp).Row
MinRowD = 2
'---> 1) If the unique identifier exist in both Checksheets-All and Orbit Dump then:
' a) Put "Completed" in Col X in sheet Checksheets-All
' b) Put today's date in Col U of sheet Orbit Dump.
'---> a) Put "Completed" in Col X in sheet Checksheets-All
' Create Formula to find items in Checksheets-ALL and in Orbit Dump
' =IF(ISERROR(VLOOKUP(A2,Orbit Dump!A:A,1)),"",A2)
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowC & ",'Orbit Dump'!A:A,1)),"""",A" & MinRowC & ")"
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Copy
WSC.Range("ZZ" & MinRowC).PasteSpecial xlPasteValues
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC).Sort Key1:=WSC.Range("ZZ" & MinRowC), order1:=xlDescending, Header:=xlNo
If WSC.Range("ZZ" & MinRowC) <> "" Then
Set cCell = WSC.Range("ZZ" & MinRowC & ":ZZ" & WSC.Rows.Count).Find(what:="")
CRow = cCell.Row - 1
WSC.Range("X" & MinRowC & ":X" & CRow).Value = "Completed"
End If
WSC.Range("ZZ:ZZ").Delete
'---> b)Put today's date in Col U of sheet Orbit Dump.
'---> Create Formula to find items in both Orbit Dump and in Checksheets-ALL
' =IF(ISERROR(VLOOKUP(A2,Checksheets-ALL!A:A,1)),"",A2)
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowD & ",'Checksheets-ALL'!A:A,1)),"""",A" & MinRowD & ")"
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Copy
WSD.Range("ZZ" & MinRowD).PasteSpecial xlPasteValues
WSD.Range("A" & MinRowD & ":ZZ" & MaxRowD).Sort Key1:=WSD.Range("ZZ" & MinRowD), order1:=xlDescending, Header:=xlNo
If WSD.Range("ZZ" & MinRowD) <> "" Then
Set cCell = WSD.Range("ZZ" & MinRowD & ":ZZ" & WSD.Rows.Count).Find(what:="")
DRow = cCell.Row - 1
WSD.Range("U" & MinRowD & ":U" & DRow).Value = DateValue(Now)
End If
WSD.Range("ZZ:ZZ").Delete
'---> 2) If the unique identifier exist in sheet Orbit Dump but DOES not exist in sheet Checksheets-All then
' Add the item in Col A of sheet Checksheets-All and put today's date in Col Y
'---> Run check on Orbit Dump
'---> Create Formula to find items in Orbit Dump but not in Checksheets-ALL
' =IF(ISERROR(VLOOKUP(A2,Control!A:A,1)),A2,"")
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowD & ",'Checksheets-ALL'!A:A,1)),A" & MinRowD & ","""")"
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Copy
WSD.Range("ZZ" & MinRowD).PasteSpecial xlPasteValues
WSD.Range("A" & MinRowD & ":ZZ" & MaxRowD).Sort Key1:=WSD.Range("ZZ" & MinRowD), order1:=xlDescending, Header:=xlNo
If WSD.Range("ZZ" & MinRowD) <> "" Then
Set cCell = WSD.Range("ZZ" & MinRowD & ":ZZ" & WSD.Rows.Count).Find(what:="")
DRow = cCell.Row - 1
WSD.Range("ZZ" & MinRowD & ":ZZ" & DRow).Copy WSC.Range("A" & MaxRowC + 1)
WSC.Range("Y" & MaxRowC + 1 & ":Y" & MaxRowC + 1 + DRow - 1).Value = DateValue(Now)
End If
WSD.Range("ZZ:ZZ").Delete
'---> 3) If the Unique Number exist in sheet Checksheets-All but DOES not exist in sheet Orbit Dump
' it just needs to show "Removed" in Column X "Check Sheet Status" of sheet Checksheets-All
' Create Formula to find items in Checksheets-ALL and NOT in Orbit Dump
' =IF(ISERROR(VLOOKUP(A2,Orbit Dump!A:A,1)),A2,"")
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowC & ",'Orbit Dump'!A:A,1)),A" & MinRowC & ","""")"
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Copy
WSC.Range("ZZ" & MinRowC).PasteSpecial xlPasteValues
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC).Sort Key1:=WSC.Range("ZZ" & MinRowC), order1:=xlDescending, Header:=xlNo
If WSC.Range("ZZ" & MinRowC) <> "" Then
Set cCell = WSC.Range("ZZ" & MinRowC & ":ZZ" & WSC.Rows.Count).Find(what:="")
CRow = cCell.Row - 1
WSC.Range("X" & MinRowC & ":X" & CRow).Value = "Removed"
End If
WSC.Range("ZZ:ZZ").Delete
'---> Sort Control on Col A
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC + DRow).Sort Key1:=WSC.Range("A" & MinRowC), order1:=xlAscending, Header:=xlNo
WSC.Range("A" & MinRowC).Select
'---> Ensable Events
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
MsgBox "Daily Update Done !", vbExclamation
End Sub
Pls let me know outcome.
gowflow
Greetings,
I cleaned out the Modules you advised on.
I cleared all fields and ran the macro to see what would be updated.
There is a lot of empty cells.
The mapping I provided did not seem to be incorporated.
I have again shown the mapping, this time in Row 6 of "Checksheets-ALL", and where that data should come from.....
As you can see, there is lots of empty columns.....
Check-Sheet-Log-Template---Copy.xlsm
I cleaned out the Modules you advised on.
I cleared all fields and ran the macro to see what would be updated.
There is a lot of empty cells.
The mapping I provided did not seem to be incorporated.
I have again shown the mapping, this time in Row 6 of "Checksheets-ALL", and where that data should come from.....
As you can see, there is lots of empty columns.....
Check-Sheet-Log-Template---Copy.xlsm
Active today
It seems we cannot communicate !!!!
for sure no mapping was taken into consideration as I clearly asked what need to be done in this question and you answered in ID ID: 40564539:
and I answered in ID: 40564680
I expressly mentioned Mapping coz you did not referred to it, but here again your answer was in ID: 40564844
Again not mentioning anything about Mapping so I went forward and developed it to update Col X with "Removed"
and now you mention Mapping ??????
Sorry but this is not professional at all you need to read the threads to the end.
I am totally upset !!!
gowflow
for sure no mapping was taken into consideration as I clearly asked what need to be done in this question and you answered in ID ID: 40564539:
If the Unique Number is no longer in Orbit, it can stay in Checksheets-ALL as well as the actuals, it just needs to show "Removed" or "Deleted" in Column X "Check Sheet Status"
and I answered in ID: 40564680
I guess Daily Update is the one we already developed.
Where do you want this new macro to be kicked of ? shall we add a new button or it to be activated after completion of Daily Update with no new button ??
And what I see there is no use of the Mapping so far in this question at this point. Right ??
gowflow
I expressly mentioned Mapping coz you did not referred to it, but here again your answer was in ID: 40564844
The macro can come after "Daily Update"
The mapping is part of the final Daily update.
Again not mentioning anything about Mapping so I went forward and developed it to update Col X with "Removed"
and now you mention Mapping ??????
Sorry but this is not professional at all you need to read the threads to the end.
I am totally upset !!!
gowflow
Active today
This is the new Daily Update that takes into consideration Mapping for new records in Orbit Dumb and not in CheckSheets-All. Sheet Mapping has been added to the workbook. MC Tracker is not part of this solution and should be treated separately.
Here is the new Daily Update code
The new version is in the updated workbook.
gowflow
Check-Sheet-Log-Template-V02.xlsm
Here is the new Daily Update code
Sub DailyUpdate()
Dim WSC As Worksheet
Dim WSD As Worksheet
Dim WSM As Worksheet
Dim MaxRowC As Long, MaxRowD As Long, MaxRowM As Long, I As Long
Dim MinRowC As Long, MinRowD As Long
Dim CRow As Long, DRow As Long
Dim cCell As Range
Dim sColD As String, sColC As String
'---> Disable Events
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
'---> Set Variables
Set WSC = Sheets("Checksheets-ALL")
MaxRowC = WSC.Range("A" & WSC.Rows.Count).End(xlUp).Row
MinRowC = 9
Set WSD = Sheets("Orbit Dump")
MaxRowD = WSD.Range("A" & WSD.Rows.Count).End(xlUp).Row
MinRowD = 2
Set WSM = Sheets("Mapping")
MaxRowM = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row
'---> 1) If the unique identifier exist in both Checksheets-All and Orbit Dump then:
' a) Put "Completed" in Col X in sheet Checksheets-All
' b) Put today's date in Col U of sheet Orbit Dump.
'---> a) Put "Completed" in Col X in sheet Checksheets-All
' Create Formula to find items in Checksheets-ALL and in Orbit Dump
' =IF(ISERROR(VLOOKUP(A2,Orbit Dump!A:A,1)),"",A2)
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowC & ",'Orbit Dump'!A:A,1)),"""",A" & MinRowC & ")"
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Copy
WSC.Range("ZZ" & MinRowC).PasteSpecial xlPasteValues
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC).Sort Key1:=WSC.Range("ZZ" & MinRowC), order1:=xlDescending, Header:=xlNo
If WSC.Range("ZZ" & MinRowC) <> "" Then
Set cCell = WSC.Range("ZZ" & MinRowC & ":ZZ" & WSC.Rows.Count).Find(what:="")
CRow = cCell.Row - 1
WSC.Range("X" & MinRowC & ":X" & CRow).Value = "Completed"
End If
WSC.Range("ZZ:ZZ").Delete
'---> b)Put today's date in Col U of sheet Orbit Dump.
'---> Create Formula to find items in both Orbit Dump and in Checksheets-ALL
' =IF(ISERROR(VLOOKUP(A2,Checksheets-ALL!A:A,1)),"",A2)
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowD & ",'Checksheets-ALL'!A:A,1)),"""",A" & MinRowD & ")"
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Copy
WSD.Range("ZZ" & MinRowD).PasteSpecial xlPasteValues
WSD.Range("A" & MinRowD & ":ZZ" & MaxRowD).Sort Key1:=WSD.Range("ZZ" & MinRowD), order1:=xlDescending, Header:=xlNo
If WSD.Range("ZZ" & MinRowD) <> "" Then
Set cCell = WSD.Range("ZZ" & MinRowD & ":ZZ" & WSD.Rows.Count).Find(what:="")
DRow = cCell.Row - 1
WSD.Range("U" & MinRowD & ":U" & DRow).Value = DateValue(Now)
End If
WSD.Range("ZZ:ZZ").Delete
'---> 2) If the unique identifier exist in sheet Orbit Dump but DOES not exist in sheet Checksheets-All then
' Add the item in Col A of sheet Checksheets-All and put today's date in Col Y
'---> Run check on Orbit Dump
'---> Create Formula to find items in Orbit Dump but not in Checksheets-ALL
' a) Create the Record in Checksheets-ALL
' =IF(ISERROR(VLOOKUP(A2,Control!A:A,1)),A2,"")
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowD & ",'Checksheets-ALL'!A:A,1)),A" & MinRowD & ","""")"
WSD.Range("ZZ" & MinRowD & ":ZZ" & MaxRowD).Copy
WSD.Range("ZZ" & MinRowD).PasteSpecial xlPasteValues
WSD.Range("A" & MinRowD & ":ZZ" & MaxRowD).Sort Key1:=WSD.Range("ZZ" & MinRowD), order1:=xlDescending, Header:=xlNo
If WSD.Range("ZZ" & MinRowD) <> "" Then
Set cCell = WSD.Range("ZZ" & MinRowD & ":ZZ" & WSD.Rows.Count).Find(what:="")
DRow = cCell.Row - 1
WSD.Range("ZZ" & MinRowD & ":ZZ" & DRow).Copy WSC.Range("A" & MaxRowC + 1)
WSC.Range("Y" & MaxRowC + 1 & ":Y" & MaxRowC + 1 + DRow - 1).Value = DateValue(Now)
End If
'---> b) Create the remaining fields as per Mapping in Checksheets-ALL for the created records.
For I = 2 To MaxRowM
sColD = Trim(WSM.Cells(I, "B"))
sColC = Trim(WSM.Cells(I, "A"))
'---> Copy the row's column into it's proper one in Checksheets-ALL for all
' the new created items fom Orbit Dump.
If sColC <> "A" And sColD <> "" And sColC <> "Y" Then
WSD.Range(sColD & MinRowD & ":" & sColD & DRow).Copy
WSC.Range(sColC & MaxRowC + 1).PasteSpecial xlPasteValues
End If
Next I
WSD.Range("ZZ:ZZ").Delete
'---> 3) If the Unique Number exist in sheet Checksheets-All but DOES not exist in sheet Orbit Dump
' it just needs to show "Removed" in Column X "Check Sheet Status" of sheet Checksheets-All
' Create Formula to find items in Checksheets-ALL and NOT in Orbit Dump
' =IF(ISERROR(VLOOKUP(A2,Orbit Dump!A:A,1)),A2,"")
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Formula = "=IF(ISERROR(VLOOKUP(A" & MinRowC & ",'Orbit Dump'!A:A,1)),A" & MinRowC & ","""")"
WSC.Range("ZZ" & MinRowC & ":ZZ" & MaxRowC).Copy
WSC.Range("ZZ" & MinRowC).PasteSpecial xlPasteValues
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC).Sort Key1:=WSC.Range("ZZ" & MinRowC), order1:=xlDescending, Header:=xlNo
If WSC.Range("ZZ" & MinRowC) <> "" Then
Set cCell = WSC.Range("ZZ" & MinRowC & ":ZZ" & WSC.Rows.Count).Find(what:="")
CRow = cCell.Row - 1
WSC.Range("X" & MinRowC & ":X" & CRow).Value = "Removed"
End If
WSC.Range("ZZ:ZZ").Delete
'---> Sort Control on Col A
WSC.Range("A" & MinRowC & ":ZZ" & MaxRowC + DRow).Sort Key1:=WSC.Range("A" & MinRowC), order1:=xlAscending, Header:=xlNo
WSC.Range("A" & MinRowC).Select
'---> Ensable Events
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
MsgBox "Daily Update Done !", vbExclamation
End Sub
The new version is in the updated workbook.
gowflow
Check-Sheet-Log-Template-V02.xlsm
The next step should be how to incorporate "MC Tracking Data" into the update. Let me know how to link this to the next question.....
Active today
when you draft you next question first you put this question is related to previous or continuation of previous and you copy paste the address of this question (the http ...) and paste it in the text of the question and explain what you want the question to be.
Once you finish drafting the question and you post it then you copy the address of the new question and paste it here that is if you want my help in the new question.
Note try to avoid putting this humongous file in the new question as you will be multiplying your personal info. Note that moderators did not remove your file it seems they need a reminder !
gowflow
Once you finish drafting the question and you post it then you copy the address of the new question and paste it here that is if you want my help in the new question.
Note try to avoid putting this humongous file in the new question as you will be multiplying your personal info. Note that moderators did not remove your file it seems they need a reminder !
gowflow
Featured Post
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Outline
Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
Suggested Courses
Course of the Month2 days, 16 hours left to enroll
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.