Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan

Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan

Makes great sense. Thanks very much. In response to your responses:

#1 - you've actually made it all pretty easy by providing the check on the
worksheet - just wondering if that's actually in a cell or is in a checkbox
from one of the toolboxes (like Forms or Control Toolbox) - if so would kind
of need to know the 'Cell Link' property for them - I would assume that it is
probably a cell in a specific column on the same row with data that changed?
But if this is actually a formula in a cell on the row that displays a
checkmark character, then just need to know the column it's in.

#2 - Not a problem, having identified which rows have changes enables an
easy move of new data, although I do wonder if just linking from Master Sheet
to related cells on Updated Data sheet wouldn't work also? Although I can
think of reasons for having it 'hard coded' so that if you remove all info on
Update Sheet, it all doesn't disappear from the Master List.

#3 - That's great news!

I can look at this some more this evening - on my way back to the office
now, and if someone else doesn't beat me to a solution, I can work one up
this evening.


"unouwanme" wrote:

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan

I'll publish the code here, but this forum is going to really mess up long
code lines. So I've also uploaded the workbook with the code in it that I
used to develop and test it, you can get that at
http://www.jlathamsite.com/uploads/UpdateTheMaster.xls

I think you probably already know, but to get in and view the code quickly,
use [Alt]+[F11] to get to the VB Editor quickly. Code is in Module named
Module1.

This is more 'complex' than it may need to be in your actual application
because I've made it very generic - allowing you to define things in it to
work by simply copying the code into your workbook and altering some
variables that are defined in the code such as the sheet names involved and
the column identification letters for the data columns on each sheet.

Hope this helps some.

Sub UpdateMasterList()
Dim UpdateSheet As String ' for name of Update sheet
Dim MasterSheet As String ' for name of the Master sheet
Dim TestForChangeColumn As String ' column with change indication in it
'variables to identify location of data on the 2 sheets
Dim CC_UColumn As String ' column with Cost Centre entries on Update sheet
Dim CC_MColumn As String ' column with Cost Centre entries on the Master
Sheet
Dim DI_1_UColumn As String ' column Months is in on Update sheet
Dim DI_1_MColumn As String ' column Months is in on Master sheet
Dim DI_2_UColumn As String ' column original budget is in on Update sheet
Dim DI_2_MColumn As String ' column original budget is in on Master sheet
Dim DI_3_UColumn As String ' column current exp. is in on Update sheet
Dim DI_3_MColumn As String ' column current exp. is in on Master sheet
Dim DI_4_UColumn As String ' column income budget is in on Update sheet
Dim DI_4_MColumn As String ' column income budget is in on Master sheet
Dim DI_5_UColumn As String ' column current budget is in on Update sheet
Dim DI_5_MColumn As String ' column current budget is in on Master sheet
Dim DI_6_UColumn As String ' column cost code is in on Update sheet
Dim DI_6_MColumn As String ' column cost code is in on Master sheet
Dim DI_7_UColumn As String ' column cost narrative is in on Update sheet
Dim DI_7_MColumn As String ' column cost narrative is in on Master sheet
'variables to hold data from Update to move to Master
Dim DI_1 As Integer ' for months
Dim DI_2 As Currency ' original budget
Dim DI_3 As Currency ' current expenditure
Dim DI_4 As Currency ' income budget
Dim DI_5 As Currency ' Current budget
Dim DI_6 As TextBox ' presume may need leading zeros
Dim DI_7 As TextBox ' CostNarrative

Dim MasterStartRow As Long ' to be defined by end user
Dim MasterEndRow As Long ' program will determine
Dim MasterUsedRange As String ' program will determine
Dim UpdateStartRow As Long ' to be defined by end user
Dim UpdateUsedRange As String ' program will determine
Dim FoundIt As Range ' for use during search for new entries
Dim WhatToFind As String ' for use during search for new entries
Dim SearchRowOffset As Long ' for use during search for new/altered
entries

'change all of these as required for your real world needs
UpdateSheet = "Updated" ' name of the sheet
MasterSheet = "Master" ' name of the sheet
MasterStartRow = 2 ' first row on Master sheet with Cost Centre entry in
it
UpdateStartRow = 2 ' first row on Update sheet with Cost Centre entry in
it
CC_UColumn = "A"
CC_MColumn = "A" ' ok, they're the same in this case
DI_1_UColumn = "B" ' column Months is in on Update sheet
DI_1_MColumn = "B" ' column Months is in on Master sheet
DI_2_UColumn = "C" ' column original budget is in on Update sheet
DI_2_MColumn = "C" ' column original budget is in on Master sheet
DI_3_UColumn = "D" ' column current exp. is in on Update sheet
DI_3_MColumn = "D" ' column current exp. is in on Master sheet
DI_4_UColumn = "E" ' column income budget is in on Update sheet
DI_4_MColumn = "E" ' column income budget is in on Master sheet
DI_5_UColumn = "F" ' column current budget is in on Update sheet
DI_5_MColumn = "F" ' column current budget is in on Master sheet
DI_6_UColumn = "G" ' column cost code is in on Update sheet
DI_6_MColumn = "G" ' column cost code is in on Master sheet
DI_7_UColumn = "H" ' column cost narrative is in on Update sheet
DI_7_MColumn = "H" ' column cost narrative is in on Master sheet
TestForChangeColumn = "I" ' column on Update sheet where change is
indicated

'determine range for searching on the Master list sheet
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
'go check for new entries on Update list sheet
Worksheets(UpdateSheet).Select
'while new entries are most likely to be at bottom of list, in order to
'keep correlation of lists for visual inspection, start at top and work
to end
Range(CC_UColumn & UpdateStartRow).Select
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
WhatToFind = ActiveCell.Offset(SearchRowOffset, 0).Value
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
'new item, add to Master sheet
Worksheets(MasterSheet).Range(CC_MColumn & MasterEndRow + 1) =
Range(CC_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_1_MColumn & MasterEndRow + 1) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & MasterEndRow + 1) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & MasterEndRow + 1) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & MasterEndRow + 1) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & MasterEndRow + 1) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & MasterEndRow + 1) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & MasterEndRow + 1) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'now also need to update MasterUsedRange
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
End If
SearchRowOffset = SearchRowOffset + 1
Loop
'next we need to check for changed items and update them
'we presume that some entry/value that we can test for is in
Range(TestForChangeColumn & UpdateStartRow).Select ' column I2 in our
sample app
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
If ActiveCell.Offset(SearchRowOffset, 0) = True Then ' change to
needed test value
' a change is indicated, move all data, to Master sheet
' don't move the Cost Centre entry
'But move based on match of Cost Centre data
WhatToFind = Range(CC_UColumn & (ActiveCell.Row +
SearchRowOffset))
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
MsgBox "A Change was indicated, but Cost Centre Data cannot
be verified, no Master Entry changed."
Else
Worksheets(MasterSheet).Range(DI_1_MColumn & FoundIt.Row) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & FoundIt.Row) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & FoundIt.Row) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & FoundIt.Row) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & FoundIt.Row) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & FoundIt.Row) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & FoundIt.Row) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'and un-mark as having been changed
'be careful - if there's actually a formula in this cell, it
will be deleted
'if it is done with a comparison formula, shouldn't have to
do anything with it
ActiveCell.Offset(SearchRowOffset, 0) = False ' set to not
changed state
End If
End If
SearchRowOffset = SearchRowOffset + 1
Loop
MsgBox "New Entries Added, Altered Entries Updated"
End Sub



"unouwanme" wrote:

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan

Hi

Thanks for that, it all works apart from the changing of the updated data.
My Formula for the checklist is as follows, there might be an easier/more
efficient way of doing this as well...

For each cell a check formula is being run whcih is :
=COUNTIF(Master!A$5:A$164,Update!A5)

then a summary is pulled together...the Check Cell:
=IF(Update!K5=0,"No",IF(Update!L5=0,"No",IF(Update !M5=0,"No",IF(Update!N5=0,"No",IF(Update!O5=0,"No" ,IF(Update!P5=0,"No",IF(Update!Q5=0,"No",IF(Update !R5=0,"No","Yes"))))))))

instead of TRUE or FALSE it will show No and Yes (TRUE = No, FALSE = Yes)

Any suggestions, have tried playing around with it, but still can't seem to
make it work.

Again thanks for the vb

"JLatham" wrote:

I'll publish the code here, but this forum is going to really mess up long
code lines. So I've also uploaded the workbook with the code in it that I
used to develop and test it, you can get that at
http://www.jlathamsite.com/uploads/UpdateTheMaster.xls

I think you probably already know, but to get in and view the code quickly,
use [Alt]+[F11] to get to the VB Editor quickly. Code is in Module named
Module1.

This is more 'complex' than it may need to be in your actual application
because I've made it very generic - allowing you to define things in it to
work by simply copying the code into your workbook and altering some
variables that are defined in the code such as the sheet names involved and
the column identification letters for the data columns on each sheet.

Hope this helps some.

Sub UpdateMasterList()
Dim UpdateSheet As String ' for name of Update sheet
Dim MasterSheet As String ' for name of the Master sheet
Dim TestForChangeColumn As String ' column with change indication in it
'variables to identify location of data on the 2 sheets
Dim CC_UColumn As String ' column with Cost Centre entries on Update sheet
Dim CC_MColumn As String ' column with Cost Centre entries on the Master
Sheet
Dim DI_1_UColumn As String ' column Months is in on Update sheet
Dim DI_1_MColumn As String ' column Months is in on Master sheet
Dim DI_2_UColumn As String ' column original budget is in on Update sheet
Dim DI_2_MColumn As String ' column original budget is in on Master sheet
Dim DI_3_UColumn As String ' column current exp. is in on Update sheet
Dim DI_3_MColumn As String ' column current exp. is in on Master sheet
Dim DI_4_UColumn As String ' column income budget is in on Update sheet
Dim DI_4_MColumn As String ' column income budget is in on Master sheet
Dim DI_5_UColumn As String ' column current budget is in on Update sheet
Dim DI_5_MColumn As String ' column current budget is in on Master sheet
Dim DI_6_UColumn As String ' column cost code is in on Update sheet
Dim DI_6_MColumn As String ' column cost code is in on Master sheet
Dim DI_7_UColumn As String ' column cost narrative is in on Update sheet
Dim DI_7_MColumn As String ' column cost narrative is in on Master sheet
'variables to hold data from Update to move to Master
Dim DI_1 As Integer ' for months
Dim DI_2 As Currency ' original budget
Dim DI_3 As Currency ' current expenditure
Dim DI_4 As Currency ' income budget
Dim DI_5 As Currency ' Current budget
Dim DI_6 As TextBox ' presume may need leading zeros
Dim DI_7 As TextBox ' CostNarrative

Dim MasterStartRow As Long ' to be defined by end user
Dim MasterEndRow As Long ' program will determine
Dim MasterUsedRange As String ' program will determine
Dim UpdateStartRow As Long ' to be defined by end user
Dim UpdateUsedRange As String ' program will determine
Dim FoundIt As Range ' for use during search for new entries
Dim WhatToFind As String ' for use during search for new entries
Dim SearchRowOffset As Long ' for use during search for new/altered
entries

'change all of these as required for your real world needs
UpdateSheet = "Updated" ' name of the sheet
MasterSheet = "Master" ' name of the sheet
MasterStartRow = 2 ' first row on Master sheet with Cost Centre entry in
it
UpdateStartRow = 2 ' first row on Update sheet with Cost Centre entry in
it
CC_UColumn = "A"
CC_MColumn = "A" ' ok, they're the same in this case
DI_1_UColumn = "B" ' column Months is in on Update sheet
DI_1_MColumn = "B" ' column Months is in on Master sheet
DI_2_UColumn = "C" ' column original budget is in on Update sheet
DI_2_MColumn = "C" ' column original budget is in on Master sheet
DI_3_UColumn = "D" ' column current exp. is in on Update sheet
DI_3_MColumn = "D" ' column current exp. is in on Master sheet
DI_4_UColumn = "E" ' column income budget is in on Update sheet
DI_4_MColumn = "E" ' column income budget is in on Master sheet
DI_5_UColumn = "F" ' column current budget is in on Update sheet
DI_5_MColumn = "F" ' column current budget is in on Master sheet
DI_6_UColumn = "G" ' column cost code is in on Update sheet
DI_6_MColumn = "G" ' column cost code is in on Master sheet
DI_7_UColumn = "H" ' column cost narrative is in on Update sheet
DI_7_MColumn = "H" ' column cost narrative is in on Master sheet
TestForChangeColumn = "I" ' column on Update sheet where change is
indicated

'determine range for searching on the Master list sheet
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
'go check for new entries on Update list sheet
Worksheets(UpdateSheet).Select
'while new entries are most likely to be at bottom of list, in order to
'keep correlation of lists for visual inspection, start at top and work
to end
Range(CC_UColumn & UpdateStartRow).Select
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
WhatToFind = ActiveCell.Offset(SearchRowOffset, 0).Value
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
'new item, add to Master sheet
Worksheets(MasterSheet).Range(CC_MColumn & MasterEndRow + 1) =
Range(CC_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_1_MColumn & MasterEndRow + 1) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & MasterEndRow + 1) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & MasterEndRow + 1) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & MasterEndRow + 1) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & MasterEndRow + 1) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & MasterEndRow + 1) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & MasterEndRow + 1) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'now also need to update MasterUsedRange
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
End If
SearchRowOffset = SearchRowOffset + 1
Loop
'next we need to check for changed items and update them
'we presume that some entry/value that we can test for is in
Range(TestForChangeColumn & UpdateStartRow).Select ' column I2 in our
sample app
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
If ActiveCell.Offset(SearchRowOffset, 0) = True Then ' change to
needed test value
' a change is indicated, move all data, to Master sheet
' don't move the Cost Centre entry
'But move based on match of Cost Centre data
WhatToFind = Range(CC_UColumn & (ActiveCell.Row +
SearchRowOffset))
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
MsgBox "A Change was indicated, but Cost Centre Data cannot
be verified, no Master Entry changed."
Else
Worksheets(MasterSheet).Range(DI_1_MColumn & FoundIt.Row) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & FoundIt.Row) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & FoundIt.Row) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & FoundIt.Row) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & FoundIt.Row) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & FoundIt.Row) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & FoundIt.Row) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'and un-mark as having been changed
'be careful - if there's actually a formula in this cell, it
will be deleted
'if it is done with a comparison formula, shouldn't have to
do anything with it
ActiveCell.Offset(SearchRowOffset, 0) = False ' set to not
changed state
End If
End If
SearchRowOffset = SearchRowOffset + 1
Loop
MsgBox "New Entries Added, Altered Entries Updated"
End Sub



"unouwanme" wrote:

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan

hi

have now fixed the problem with Yes/No-true/False so need to worry about that.

as you stated the formula in the check cell has been deleted so would it
work if i added to the vb and got it to copy and paste the formula back in
after it has completed all the checks?

"JLatham" wrote:

I'll publish the code here, but this forum is going to really mess up long
code lines. So I've also uploaded the workbook with the code in it that I
used to develop and test it, you can get that at
http://www.jlathamsite.com/uploads/UpdateTheMaster.xls

I think you probably already know, but to get in and view the code quickly,
use [Alt]+[F11] to get to the VB Editor quickly. Code is in Module named
Module1.

This is more 'complex' than it may need to be in your actual application
because I've made it very generic - allowing you to define things in it to
work by simply copying the code into your workbook and altering some
variables that are defined in the code such as the sheet names involved and
the column identification letters for the data columns on each sheet.

Hope this helps some.

Sub UpdateMasterList()
Dim UpdateSheet As String ' for name of Update sheet
Dim MasterSheet As String ' for name of the Master sheet
Dim TestForChangeColumn As String ' column with change indication in it
'variables to identify location of data on the 2 sheets
Dim CC_UColumn As String ' column with Cost Centre entries on Update sheet
Dim CC_MColumn As String ' column with Cost Centre entries on the Master
Sheet
Dim DI_1_UColumn As String ' column Months is in on Update sheet
Dim DI_1_MColumn As String ' column Months is in on Master sheet
Dim DI_2_UColumn As String ' column original budget is in on Update sheet
Dim DI_2_MColumn As String ' column original budget is in on Master sheet
Dim DI_3_UColumn As String ' column current exp. is in on Update sheet
Dim DI_3_MColumn As String ' column current exp. is in on Master sheet
Dim DI_4_UColumn As String ' column income budget is in on Update sheet
Dim DI_4_MColumn As String ' column income budget is in on Master sheet
Dim DI_5_UColumn As String ' column current budget is in on Update sheet
Dim DI_5_MColumn As String ' column current budget is in on Master sheet
Dim DI_6_UColumn As String ' column cost code is in on Update sheet
Dim DI_6_MColumn As String ' column cost code is in on Master sheet
Dim DI_7_UColumn As String ' column cost narrative is in on Update sheet
Dim DI_7_MColumn As String ' column cost narrative is in on Master sheet
'variables to hold data from Update to move to Master
Dim DI_1 As Integer ' for months
Dim DI_2 As Currency ' original budget
Dim DI_3 As Currency ' current expenditure
Dim DI_4 As Currency ' income budget
Dim DI_5 As Currency ' Current budget
Dim DI_6 As TextBox ' presume may need leading zeros
Dim DI_7 As TextBox ' CostNarrative

Dim MasterStartRow As Long ' to be defined by end user
Dim MasterEndRow As Long ' program will determine
Dim MasterUsedRange As String ' program will determine
Dim UpdateStartRow As Long ' to be defined by end user
Dim UpdateUsedRange As String ' program will determine
Dim FoundIt As Range ' for use during search for new entries
Dim WhatToFind As String ' for use during search for new entries
Dim SearchRowOffset As Long ' for use during search for new/altered
entries

'change all of these as required for your real world needs
UpdateSheet = "Updated" ' name of the sheet
MasterSheet = "Master" ' name of the sheet
MasterStartRow = 2 ' first row on Master sheet with Cost Centre entry in
it
UpdateStartRow = 2 ' first row on Update sheet with Cost Centre entry in
it
CC_UColumn = "A"
CC_MColumn = "A" ' ok, they're the same in this case
DI_1_UColumn = "B" ' column Months is in on Update sheet
DI_1_MColumn = "B" ' column Months is in on Master sheet
DI_2_UColumn = "C" ' column original budget is in on Update sheet
DI_2_MColumn = "C" ' column original budget is in on Master sheet
DI_3_UColumn = "D" ' column current exp. is in on Update sheet
DI_3_MColumn = "D" ' column current exp. is in on Master sheet
DI_4_UColumn = "E" ' column income budget is in on Update sheet
DI_4_MColumn = "E" ' column income budget is in on Master sheet
DI_5_UColumn = "F" ' column current budget is in on Update sheet
DI_5_MColumn = "F" ' column current budget is in on Master sheet
DI_6_UColumn = "G" ' column cost code is in on Update sheet
DI_6_MColumn = "G" ' column cost code is in on Master sheet
DI_7_UColumn = "H" ' column cost narrative is in on Update sheet
DI_7_MColumn = "H" ' column cost narrative is in on Master sheet
TestForChangeColumn = "I" ' column on Update sheet where change is
indicated

'determine range for searching on the Master list sheet
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
'go check for new entries on Update list sheet
Worksheets(UpdateSheet).Select
'while new entries are most likely to be at bottom of list, in order to
'keep correlation of lists for visual inspection, start at top and work
to end
Range(CC_UColumn & UpdateStartRow).Select
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
WhatToFind = ActiveCell.Offset(SearchRowOffset, 0).Value
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
'new item, add to Master sheet
Worksheets(MasterSheet).Range(CC_MColumn & MasterEndRow + 1) =
Range(CC_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_1_MColumn & MasterEndRow + 1) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & MasterEndRow + 1) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & MasterEndRow + 1) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & MasterEndRow + 1) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & MasterEndRow + 1) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & MasterEndRow + 1) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & MasterEndRow + 1) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'now also need to update MasterUsedRange
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
End If
SearchRowOffset = SearchRowOffset + 1
Loop
'next we need to check for changed items and update them
'we presume that some entry/value that we can test for is in
Range(TestForChangeColumn & UpdateStartRow).Select ' column I2 in our
sample app
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
If ActiveCell.Offset(SearchRowOffset, 0) = True Then ' change to
needed test value
' a change is indicated, move all data, to Master sheet
' don't move the Cost Centre entry
'But move based on match of Cost Centre data
WhatToFind = Range(CC_UColumn & (ActiveCell.Row +
SearchRowOffset))
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
MsgBox "A Change was indicated, but Cost Centre Data cannot
be verified, no Master Entry changed."
Else
Worksheets(MasterSheet).Range(DI_1_MColumn & FoundIt.Row) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & FoundIt.Row) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & FoundIt.Row) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & FoundIt.Row) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & FoundIt.Row) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & FoundIt.Row) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & FoundIt.Row) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'and un-mark as having been changed
'be careful - if there's actually a formula in this cell, it
will be deleted
'if it is done with a comparison formula, shouldn't have to
do anything with it
ActiveCell.Offset(SearchRowOffset, 0) = False ' set to not
changed state
End If
End If
SearchRowOffset = SearchRowOffset + 1
Loop
MsgBox "New Entries Added, Altered Entries Updated"
End Sub



"unouwanme" wrote:

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan

Yes, it should work using a copy of the formula and replacing it at the
appropriate place in the VB code.

Creating a formula in VB, especially a complex one like that second one you
showed, can be confusing. The formula is represented as a string like:
"=SUM(A1:A99)" (including the equal symbol)
and assigned like this
Range("A100").Formula = "=SUM(A1:A99)"
of course you could have a string variable holding the made up formula and
assign the .Formula that way like
Dim anyFormula As String
anyFormula = "=SUM(A1:A99)"
Range("A100").Formula = anyFormula

Where it gets tricky is in altering ranges/values in them and inserting
double-quotes needed within the formula itself. Simple example, a test for a
match on string "My Name Here" in another cell, say F5 from anywhere else.
The formula in the cell would look like
=IF(F5="My Name Here",True,False)
in VB you'd have to build it like this:
anyFormula = "=IF(F5=" & Chr$(34) & "My Name Here" & Chr$(34) & ",True,False)"

Note that you have to use the Chr$(34) [or just Chr(34)] to get the required
double-quotation marks into the formula. You may have to do some work to get
the proper row number included in the formula also.

But I'm thinking that if the 'needs update' indicator is based on a formula
examining the contents of all of the data items in the row, then why not just
delete the data items themselves in those columns (or set them to zero?)
after the code copies the updated entries over to the Master sheet? Then
you'd not have to worry about the formula getting written over and having to
rebuild it, it would just naturally flip from "YES" to "NO" at that time.




"unouwanme" wrote:

hi

have now fixed the problem with Yes/No-true/False so need to worry about that.

as you stated the formula in the check cell has been deleted so would it
work if i added to the vb and got it to copy and paste the formula back in
after it has completed all the checks?

"JLatham" wrote:

I'll publish the code here, but this forum is going to really mess up long
code lines. So I've also uploaded the workbook with the code in it that I
used to develop and test it, you can get that at
http://www.jlathamsite.com/uploads/UpdateTheMaster.xls

I think you probably already know, but to get in and view the code quickly,
use [Alt]+[F11] to get to the VB Editor quickly. Code is in Module named
Module1.

This is more 'complex' than it may need to be in your actual application
because I've made it very generic - allowing you to define things in it to
work by simply copying the code into your workbook and altering some
variables that are defined in the code such as the sheet names involved and
the column identification letters for the data columns on each sheet.

Hope this helps some.

Sub UpdateMasterList()
Dim UpdateSheet As String ' for name of Update sheet
Dim MasterSheet As String ' for name of the Master sheet
Dim TestForChangeColumn As String ' column with change indication in it
'variables to identify location of data on the 2 sheets
Dim CC_UColumn As String ' column with Cost Centre entries on Update sheet
Dim CC_MColumn As String ' column with Cost Centre entries on the Master
Sheet
Dim DI_1_UColumn As String ' column Months is in on Update sheet
Dim DI_1_MColumn As String ' column Months is in on Master sheet
Dim DI_2_UColumn As String ' column original budget is in on Update sheet
Dim DI_2_MColumn As String ' column original budget is in on Master sheet
Dim DI_3_UColumn As String ' column current exp. is in on Update sheet
Dim DI_3_MColumn As String ' column current exp. is in on Master sheet
Dim DI_4_UColumn As String ' column income budget is in on Update sheet
Dim DI_4_MColumn As String ' column income budget is in on Master sheet
Dim DI_5_UColumn As String ' column current budget is in on Update sheet
Dim DI_5_MColumn As String ' column current budget is in on Master sheet
Dim DI_6_UColumn As String ' column cost code is in on Update sheet
Dim DI_6_MColumn As String ' column cost code is in on Master sheet
Dim DI_7_UColumn As String ' column cost narrative is in on Update sheet
Dim DI_7_MColumn As String ' column cost narrative is in on Master sheet
'variables to hold data from Update to move to Master
Dim DI_1 As Integer ' for months
Dim DI_2 As Currency ' original budget
Dim DI_3 As Currency ' current expenditure
Dim DI_4 As Currency ' income budget
Dim DI_5 As Currency ' Current budget
Dim DI_6 As TextBox ' presume may need leading zeros
Dim DI_7 As TextBox ' CostNarrative

Dim MasterStartRow As Long ' to be defined by end user
Dim MasterEndRow As Long ' program will determine
Dim MasterUsedRange As String ' program will determine
Dim UpdateStartRow As Long ' to be defined by end user
Dim UpdateUsedRange As String ' program will determine
Dim FoundIt As Range ' for use during search for new entries
Dim WhatToFind As String ' for use during search for new entries
Dim SearchRowOffset As Long ' for use during search for new/altered
entries

'change all of these as required for your real world needs
UpdateSheet = "Updated" ' name of the sheet
MasterSheet = "Master" ' name of the sheet
MasterStartRow = 2 ' first row on Master sheet with Cost Centre entry in
it
UpdateStartRow = 2 ' first row on Update sheet with Cost Centre entry in
it
CC_UColumn = "A"
CC_MColumn = "A" ' ok, they're the same in this case
DI_1_UColumn = "B" ' column Months is in on Update sheet
DI_1_MColumn = "B" ' column Months is in on Master sheet
DI_2_UColumn = "C" ' column original budget is in on Update sheet
DI_2_MColumn = "C" ' column original budget is in on Master sheet
DI_3_UColumn = "D" ' column current exp. is in on Update sheet
DI_3_MColumn = "D" ' column current exp. is in on Master sheet
DI_4_UColumn = "E" ' column income budget is in on Update sheet
DI_4_MColumn = "E" ' column income budget is in on Master sheet
DI_5_UColumn = "F" ' column current budget is in on Update sheet
DI_5_MColumn = "F" ' column current budget is in on Master sheet
DI_6_UColumn = "G" ' column cost code is in on Update sheet
DI_6_MColumn = "G" ' column cost code is in on Master sheet
DI_7_UColumn = "H" ' column cost narrative is in on Update sheet
DI_7_MColumn = "H" ' column cost narrative is in on Master sheet
TestForChangeColumn = "I" ' column on Update sheet where change is
indicated

'determine range for searching on the Master list sheet
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
'go check for new entries on Update list sheet
Worksheets(UpdateSheet).Select
'while new entries are most likely to be at bottom of list, in order to
'keep correlation of lists for visual inspection, start at top and work
to end
Range(CC_UColumn & UpdateStartRow).Select
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
WhatToFind = ActiveCell.Offset(SearchRowOffset, 0).Value
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
'new item, add to Master sheet
Worksheets(MasterSheet).Range(CC_MColumn & MasterEndRow + 1) =
Range(CC_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_1_MColumn & MasterEndRow + 1) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & MasterEndRow + 1) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & MasterEndRow + 1) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & MasterEndRow + 1) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & MasterEndRow + 1) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & MasterEndRow + 1) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & MasterEndRow + 1) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'now also need to update MasterUsedRange
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
End If
SearchRowOffset = SearchRowOffset + 1
Loop
'next we need to check for changed items and update them
'we presume that some entry/value that we can test for is in
Range(TestForChangeColumn & UpdateStartRow).Select ' column I2 in our
sample app
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
If ActiveCell.Offset(SearchRowOffset, 0) = True Then ' change to
needed test value
' a change is indicated, move all data, to Master sheet
' don't move the Cost Centre entry
'But move based on match of Cost Centre data
WhatToFind = Range(CC_UColumn & (ActiveCell.Row +
SearchRowOffset))
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
MsgBox "A Change was indicated, but Cost Centre Data cannot
be verified, no Master Entry changed."
Else
Worksheets(MasterSheet).Range(DI_1_MColumn & FoundIt.Row) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & FoundIt.Row) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & FoundIt.Row) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & FoundIt.Row) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & FoundIt.Row) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & FoundIt.Row) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & FoundIt.Row) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'and un-mark as having been changed
'be careful - if there's actually a formula in this cell, it
will be deleted
'if it is done with a comparison formula, shouldn't have to
do anything with it
ActiveCell.Offset(SearchRowOffset, 0) = False ' set to not
changed state
End If
End If
SearchRowOffset = SearchRowOffset + 1
Loop
MsgBox "New Entries Added, Altered Entries Updated"
End Sub



"unouwanme" wrote:

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Ryan

THANKS NOW COMPLETELY WORKS....FOR NOW...

if i got any further problems with excel, would it be ok to e-mail you?

"JLatham" wrote:

Yes, it should work using a copy of the formula and replacing it at the
appropriate place in the VB code.

Creating a formula in VB, especially a complex one like that second one you
showed, can be confusing. The formula is represented as a string like:
"=SUM(A1:A99)" (including the equal symbol)
and assigned like this
Range("A100").Formula = "=SUM(A1:A99)"
of course you could have a string variable holding the made up formula and
assign the .Formula that way like
Dim anyFormula As String
anyFormula = "=SUM(A1:A99)"
Range("A100").Formula = anyFormula

Where it gets tricky is in altering ranges/values in them and inserting
double-quotes needed within the formula itself. Simple example, a test for a
match on string "My Name Here" in another cell, say F5 from anywhere else.
The formula in the cell would look like
=IF(F5="My Name Here",True,False)
in VB you'd have to build it like this:
anyFormula = "=IF(F5=" & Chr$(34) & "My Name Here" & Chr$(34) & ",True,False)"

Note that you have to use the Chr$(34) [or just Chr(34)] to get the required
double-quotation marks into the formula. You may have to do some work to get
the proper row number included in the formula also.

But I'm thinking that if the 'needs update' indicator is based on a formula
examining the contents of all of the data items in the row, then why not just
delete the data items themselves in those columns (or set them to zero?)
after the code copies the updated entries over to the Master sheet? Then
you'd not have to worry about the formula getting written over and having to
rebuild it, it would just naturally flip from "YES" to "NO" at that time.




"unouwanme" wrote:

hi

have now fixed the problem with Yes/No-true/False so need to worry about that.

as you stated the formula in the check cell has been deleted so would it
work if i added to the vb and got it to copy and paste the formula back in
after it has completed all the checks?

"JLatham" wrote:

I'll publish the code here, but this forum is going to really mess up long
code lines. So I've also uploaded the workbook with the code in it that I
used to develop and test it, you can get that at
http://www.jlathamsite.com/uploads/UpdateTheMaster.xls

I think you probably already know, but to get in and view the code quickly,
use [Alt]+[F11] to get to the VB Editor quickly. Code is in Module named
Module1.

This is more 'complex' than it may need to be in your actual application
because I've made it very generic - allowing you to define things in it to
work by simply copying the code into your workbook and altering some
variables that are defined in the code such as the sheet names involved and
the column identification letters for the data columns on each sheet.

Hope this helps some.

Sub UpdateMasterList()
Dim UpdateSheet As String ' for name of Update sheet
Dim MasterSheet As String ' for name of the Master sheet
Dim TestForChangeColumn As String ' column with change indication in it
'variables to identify location of data on the 2 sheets
Dim CC_UColumn As String ' column with Cost Centre entries on Update sheet
Dim CC_MColumn As String ' column with Cost Centre entries on the Master
Sheet
Dim DI_1_UColumn As String ' column Months is in on Update sheet
Dim DI_1_MColumn As String ' column Months is in on Master sheet
Dim DI_2_UColumn As String ' column original budget is in on Update sheet
Dim DI_2_MColumn As String ' column original budget is in on Master sheet
Dim DI_3_UColumn As String ' column current exp. is in on Update sheet
Dim DI_3_MColumn As String ' column current exp. is in on Master sheet
Dim DI_4_UColumn As String ' column income budget is in on Update sheet
Dim DI_4_MColumn As String ' column income budget is in on Master sheet
Dim DI_5_UColumn As String ' column current budget is in on Update sheet
Dim DI_5_MColumn As String ' column current budget is in on Master sheet
Dim DI_6_UColumn As String ' column cost code is in on Update sheet
Dim DI_6_MColumn As String ' column cost code is in on Master sheet
Dim DI_7_UColumn As String ' column cost narrative is in on Update sheet
Dim DI_7_MColumn As String ' column cost narrative is in on Master sheet
'variables to hold data from Update to move to Master
Dim DI_1 As Integer ' for months
Dim DI_2 As Currency ' original budget
Dim DI_3 As Currency ' current expenditure
Dim DI_4 As Currency ' income budget
Dim DI_5 As Currency ' Current budget
Dim DI_6 As TextBox ' presume may need leading zeros
Dim DI_7 As TextBox ' CostNarrative

Dim MasterStartRow As Long ' to be defined by end user
Dim MasterEndRow As Long ' program will determine
Dim MasterUsedRange As String ' program will determine
Dim UpdateStartRow As Long ' to be defined by end user
Dim UpdateUsedRange As String ' program will determine
Dim FoundIt As Range ' for use during search for new entries
Dim WhatToFind As String ' for use during search for new entries
Dim SearchRowOffset As Long ' for use during search for new/altered
entries

'change all of these as required for your real world needs
UpdateSheet = "Updated" ' name of the sheet
MasterSheet = "Master" ' name of the sheet
MasterStartRow = 2 ' first row on Master sheet with Cost Centre entry in
it
UpdateStartRow = 2 ' first row on Update sheet with Cost Centre entry in
it
CC_UColumn = "A"
CC_MColumn = "A" ' ok, they're the same in this case
DI_1_UColumn = "B" ' column Months is in on Update sheet
DI_1_MColumn = "B" ' column Months is in on Master sheet
DI_2_UColumn = "C" ' column original budget is in on Update sheet
DI_2_MColumn = "C" ' column original budget is in on Master sheet
DI_3_UColumn = "D" ' column current exp. is in on Update sheet
DI_3_MColumn = "D" ' column current exp. is in on Master sheet
DI_4_UColumn = "E" ' column income budget is in on Update sheet
DI_4_MColumn = "E" ' column income budget is in on Master sheet
DI_5_UColumn = "F" ' column current budget is in on Update sheet
DI_5_MColumn = "F" ' column current budget is in on Master sheet
DI_6_UColumn = "G" ' column cost code is in on Update sheet
DI_6_MColumn = "G" ' column cost code is in on Master sheet
DI_7_UColumn = "H" ' column cost narrative is in on Update sheet
DI_7_MColumn = "H" ' column cost narrative is in on Master sheet
TestForChangeColumn = "I" ' column on Update sheet where change is
indicated

'determine range for searching on the Master list sheet
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
'go check for new entries on Update list sheet
Worksheets(UpdateSheet).Select
'while new entries are most likely to be at bottom of list, in order to
'keep correlation of lists for visual inspection, start at top and work
to end
Range(CC_UColumn & UpdateStartRow).Select
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
WhatToFind = ActiveCell.Offset(SearchRowOffset, 0).Value
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
'new item, add to Master sheet
Worksheets(MasterSheet).Range(CC_MColumn & MasterEndRow + 1) =
Range(CC_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_1_MColumn & MasterEndRow + 1) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & MasterEndRow + 1) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & MasterEndRow + 1) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & MasterEndRow + 1) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & MasterEndRow + 1) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & MasterEndRow + 1) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & MasterEndRow + 1) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'now also need to update MasterUsedRange
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
End If
SearchRowOffset = SearchRowOffset + 1
Loop
'next we need to check for changed items and update them
'we presume that some entry/value that we can test for is in
Range(TestForChangeColumn & UpdateStartRow).Select ' column I2 in our
sample app
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
If ActiveCell.Offset(SearchRowOffset, 0) = True Then ' change to
needed test value
' a change is indicated, move all data, to Master sheet
' don't move the Cost Centre entry
'But move based on match of Cost Centre data
WhatToFind = Range(CC_UColumn & (ActiveCell.Row +
SearchRowOffset))
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
MsgBox "A Change was indicated, but Cost Centre Data cannot
be verified, no Master Entry changed."
Else
Worksheets(MasterSheet).Range(DI_1_MColumn & FoundIt.Row) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & FoundIt.Row) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & FoundIt.Row) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & FoundIt.Row) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & FoundIt.Row) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & FoundIt.Row) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & FoundIt.Row) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'and un-mark as having been changed
'be careful - if there's actually a formula in this cell, it
will be deleted
'if it is done with a comparison formula, shouldn't have to
do anything with it
ActiveCell.Offset(SearchRowOffset, 0) = False ' set to not
changed state
End If
End If
SearchRowOffset = SearchRowOffset + 1
Loop
MsgBox "New Entries Added, Altered Entries Updated"
End Sub



"unouwanme" wrote:

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums
with the amended item(s) from the 'updated data'.

Does this make sense, would really appreciate any help/guidance with this.

many thanks

Sure, take out the spaces and
jlatham @ jlathamsite.com
will get it to me. Glad you thought enough of what I've done so far as to
risk getting in deeper with me <g.

"unouwanme" wrote:

THANKS NOW COMPLETELY WORKS....FOR NOW...

if i got any further problems with excel, would it be ok to e-mail you?

"JLatham" wrote:

Yes, it should work using a copy of the formula and replacing it at the
appropriate place in the VB code.

Creating a formula in VB, especially a complex one like that second one you
showed, can be confusing. The formula is represented as a string like:
"=SUM(A1:A99)" (including the equal symbol)
and assigned like this
Range("A100").Formula = "=SUM(A1:A99)"
of course you could have a string variable holding the made up formula and
assign the .Formula that way like
Dim anyFormula As String
anyFormula = "=SUM(A1:A99)"
Range("A100").Formula = anyFormula

Where it gets tricky is in altering ranges/values in them and inserting
double-quotes needed within the formula itself. Simple example, a test for a
match on string "My Name Here" in another cell, say F5 from anywhere else.
The formula in the cell would look like
=IF(F5="My Name Here",True,False)
in VB you'd have to build it like this:
anyFormula = "=IF(F5=" & Chr$(34) & "My Name Here" & Chr$(34) & ",True,False)"

Note that you have to use the Chr$(34) [or just Chr(34)] to get the required
double-quotation marks into the formula. You may have to do some work to get
the proper row number included in the formula also.

But I'm thinking that if the 'needs update' indicator is based on a formula
examining the contents of all of the data items in the row, then why not just
delete the data items themselves in those columns (or set them to zero?)
after the code copies the updated entries over to the Master sheet? Then
you'd not have to worry about the formula getting written over and having to
rebuild it, it would just naturally flip from "YES" to "NO" at that time.




"unouwanme" wrote:

hi

have now fixed the problem with Yes/No-true/False so need to worry about that.

as you stated the formula in the check cell has been deleted so would it
work if i added to the vb and got it to copy and paste the formula back in
after it has completed all the checks?

"JLatham" wrote:

I'll publish the code here, but this forum is going to really mess up long
code lines. So I've also uploaded the workbook with the code in it that I
used to develop and test it, you can get that at
http://www.jlathamsite.com/uploads/UpdateTheMaster.xls

I think you probably already know, but to get in and view the code quickly,
use [Alt]+[F11] to get to the VB Editor quickly. Code is in Module named
Module1.

This is more 'complex' than it may need to be in your actual application
because I've made it very generic - allowing you to define things in it to
work by simply copying the code into your workbook and altering some
variables that are defined in the code such as the sheet names involved and
the column identification letters for the data columns on each sheet.

Hope this helps some.

Sub UpdateMasterList()
Dim UpdateSheet As String ' for name of Update sheet
Dim MasterSheet As String ' for name of the Master sheet
Dim TestForChangeColumn As String ' column with change indication in it
'variables to identify location of data on the 2 sheets
Dim CC_UColumn As String ' column with Cost Centre entries on Update sheet
Dim CC_MColumn As String ' column with Cost Centre entries on the Master
Sheet
Dim DI_1_UColumn As String ' column Months is in on Update sheet
Dim DI_1_MColumn As String ' column Months is in on Master sheet
Dim DI_2_UColumn As String ' column original budget is in on Update sheet
Dim DI_2_MColumn As String ' column original budget is in on Master sheet
Dim DI_3_UColumn As String ' column current exp. is in on Update sheet
Dim DI_3_MColumn As String ' column current exp. is in on Master sheet
Dim DI_4_UColumn As String ' column income budget is in on Update sheet
Dim DI_4_MColumn As String ' column income budget is in on Master sheet
Dim DI_5_UColumn As String ' column current budget is in on Update sheet
Dim DI_5_MColumn As String ' column current budget is in on Master sheet
Dim DI_6_UColumn As String ' column cost code is in on Update sheet
Dim DI_6_MColumn As String ' column cost code is in on Master sheet
Dim DI_7_UColumn As String ' column cost narrative is in on Update sheet
Dim DI_7_MColumn As String ' column cost narrative is in on Master sheet
'variables to hold data from Update to move to Master
Dim DI_1 As Integer ' for months
Dim DI_2 As Currency ' original budget
Dim DI_3 As Currency ' current expenditure
Dim DI_4 As Currency ' income budget
Dim DI_5 As Currency ' Current budget
Dim DI_6 As TextBox ' presume may need leading zeros
Dim DI_7 As TextBox ' CostNarrative

Dim MasterStartRow As Long ' to be defined by end user
Dim MasterEndRow As Long ' program will determine
Dim MasterUsedRange As String ' program will determine
Dim UpdateStartRow As Long ' to be defined by end user
Dim UpdateUsedRange As String ' program will determine
Dim FoundIt As Range ' for use during search for new entries
Dim WhatToFind As String ' for use during search for new entries
Dim SearchRowOffset As Long ' for use during search for new/altered
entries

'change all of these as required for your real world needs
UpdateSheet = "Updated" ' name of the sheet
MasterSheet = "Master" ' name of the sheet
MasterStartRow = 2 ' first row on Master sheet with Cost Centre entry in
it
UpdateStartRow = 2 ' first row on Update sheet with Cost Centre entry in
it
CC_UColumn = "A"
CC_MColumn = "A" ' ok, they're the same in this case
DI_1_UColumn = "B" ' column Months is in on Update sheet
DI_1_MColumn = "B" ' column Months is in on Master sheet
DI_2_UColumn = "C" ' column original budget is in on Update sheet
DI_2_MColumn = "C" ' column original budget is in on Master sheet
DI_3_UColumn = "D" ' column current exp. is in on Update sheet
DI_3_MColumn = "D" ' column current exp. is in on Master sheet
DI_4_UColumn = "E" ' column income budget is in on Update sheet
DI_4_MColumn = "E" ' column income budget is in on Master sheet
DI_5_UColumn = "F" ' column current budget is in on Update sheet
DI_5_MColumn = "F" ' column current budget is in on Master sheet
DI_6_UColumn = "G" ' column cost code is in on Update sheet
DI_6_MColumn = "G" ' column cost code is in on Master sheet
DI_7_UColumn = "H" ' column cost narrative is in on Update sheet
DI_7_MColumn = "H" ' column cost narrative is in on Master sheet
TestForChangeColumn = "I" ' column on Update sheet where change is
indicated

'determine range for searching on the Master list sheet
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
'go check for new entries on Update list sheet
Worksheets(UpdateSheet).Select
'while new entries are most likely to be at bottom of list, in order to
'keep correlation of lists for visual inspection, start at top and work
to end
Range(CC_UColumn & UpdateStartRow).Select
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
WhatToFind = ActiveCell.Offset(SearchRowOffset, 0).Value
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
'new item, add to Master sheet
Worksheets(MasterSheet).Range(CC_MColumn & MasterEndRow + 1) =
Range(CC_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_1_MColumn & MasterEndRow + 1) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & MasterEndRow + 1) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & MasterEndRow + 1) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & MasterEndRow + 1) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & MasterEndRow + 1) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & MasterEndRow + 1) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & MasterEndRow + 1) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'now also need to update MasterUsedRange
MasterEndRow = Worksheets(MasterSheet).Range(CC_MColumn &
"65536").End(xlUp).Row
MasterUsedRange = CC_MColumn & MasterStartRow & ":" &
Worksheets(MasterSheet).Range(CC_MColumn & "65536").End(xlUp).Address
End If
SearchRowOffset = SearchRowOffset + 1
Loop
'next we need to check for changed items and update them
'we presume that some entry/value that we can test for is in
Range(TestForChangeColumn & UpdateStartRow).Select ' column I2 in our
sample app
SearchRowOffset = 0 ' initialize
Do While (ActiveCell.Row + SearchRowOffset) < (Range(CC_UColumn &
"65536").End(xlUp).Row + 1)
If ActiveCell.Offset(SearchRowOffset, 0) = True Then ' change to
needed test value
' a change is indicated, move all data, to Master sheet
' don't move the Cost Centre entry
'But move based on match of Cost Centre data
WhatToFind = Range(CC_UColumn & (ActiveCell.Row +
SearchRowOffset))
With Worksheets(MasterSheet).Range(MasterUsedRange)
' same as Edit | Find | In: Values, Match: Whole Word (whole
Cell contents)
Set FoundIt = .Find(WhatToFind, , xlValues, xlWhole)
End With
If FoundIt Is Nothing Then
MsgBox "A Change was indicated, but Cost Centre Data cannot
be verified, no Master Entry changed."
Else
Worksheets(MasterSheet).Range(DI_1_MColumn & FoundIt.Row) =
Range(DI_1_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_2_MColumn & FoundIt.Row) =
Range(DI_2_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_3_MColumn & FoundIt.Row) =
Range(DI_3_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_4_MColumn & FoundIt.Row) =
Range(DI_4_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_5_MColumn & FoundIt.Row) =
Range(DI_5_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_6_MColumn & FoundIt.Row) =
Range(DI_6_UColumn & (ActiveCell.Row + SearchRowOffset))
Worksheets(MasterSheet).Range(DI_7_MColumn & FoundIt.Row) =
Range(DI_7_UColumn & (ActiveCell.Row + SearchRowOffset))
'and un-mark as having been changed
'be careful - if there's actually a formula in this cell, it
will be deleted
'if it is done with a comparison formula, shouldn't have to
do anything with it
ActiveCell.Offset(SearchRowOffset, 0) = False ' set to not
changed state
End If
End If
SearchRowOffset = SearchRowOffset + 1
Loop
MsgBox "New Entries Added, Altered Entries Updated"
End Sub



"unouwanme" wrote:

Thanks for your reply...

1 - yes, i agree that won't be too difficult, am just unsure of the wording
for the macro though as i want it inserted at the end of the master list so
presume i would need an end function or something similar.

2 - Yes a change in any column would mean it is updated, i have a check on
the worksheet to check which cells are updated and which ones aren't so i
presume i can use the answer from that formula to use in the macro

Basically 8 columns a
1 - Cost Centre (00000 <narrative)
2 - Month (1-12)
3 - Original budget(<figure)
4 - current expenditure(<figure)
5 - income budget(<figure)
6 - current budget(<figure)
7 - CostCode (00000)
8 - CostNarrative (<narrative)

each month the updated data is overwritten by the new data, whcih comes from
a financial system.

3 - THe Cost Centre will/can only ever appear once in either spreadsheet.

Does this make any more sense?

"JLatham" wrote:

1 - Finding new items in the Updated Items list and adding them to the Master
Data list is a fairly easy task.

2 - Finding updated ones may not be quite as easy? What defines unpdated? You
mention having 8 columns of information, so would a change in the data in any
one of those 8 columns for an item on the Updated Items list mean it was a
change?

3 - Can the data in the Cost Centre column appear more than once on either
sheet? I'm kind of picturing that Cost Centre entries on the Master Data
list would each only appear once? And that they may appear more than once on
the Updated Data sheet and you want to update what's on the Master Data sheet
with the latest/last matching entry on the Updated Data sheet?

"unouwanme" wrote:

Hi...

Basically..

i have a worksheet with 2 sets of data, each set of data has 8 columns. The
first set of data is the 'master data', the second set is the 'updated data'.
The first column of each set of data is called "Cost Centre".

i have set up if statements to compare the 2 sets of data to find, which
item(s) of the 'updated data' are new/amended from the 'master data'.

for the new data i want a macro that can copy the new item(s) from the
'updated data' and paste it at the bottom of the 'master data'.

then i want another macro to search the "Cost Centre" column in the 'master
data' to find the matching "cost centre" and replace the 7 remaining colums