Hello,
I have a master excel sheet (One workbook) which is updated once in a
month from another sheet called datasheet (Another workbook) . Both
sheets (master and the datasheet) contain the same data. New
additions/updates/changes are added on a regular basis to the datasheet
and is then added to the master sheet at the end of the month.

The sheets consists of about 10 column headings with around 500 rows.
Now, when the datasheet is to be updated to the master sheet, I want a
macro which should find out if the data is already there (eg. it can
check this from employee code etc in the first column). If it is
already there in the master sheet, it should check whether all the
values/text associated with it are the same. If there are some changes
in the value of the cell/change in text, then the new value/text from
the other sheet should be updated to the master excel sheet.
Also, if there are new names/employee codes added to the datasheet,
then this should be updated on the master sheet on a new row.

I would be very grateful if anyone can throw some light on how to go
about this by using macro.

Thanks a ton.


--
lalthan
------------------------------------------------------------------------
lalthan's Profile: http://www.excelforum.com/member.php...o&userid=10557
View this thread: http://www.excelforum.com/showthread...hreadid=397222

Thanks Steve for the guidance.

Do you have a sample macro which list all those you have mentione
above ? If so, can you please post it?

Thanks again

--
laltha
-----------------------------------------------------------------------
lalthan's Profile: http://www.excelforum.com/member.php...fo&userid=1055
View this thread: http://www.excelforum.com/showthread.php?threadid=39722

Lalthan,

Here is some code (untested) that should check the new stuff line by line.
(this may not be the best code - but it should work)

It first checks if the entity already exists in the Old Data.
If not - than you need code to just transfer the New Stuff to the next
available line in Old Stuff.
If yes - than you need to compare each column and only change column entries
that are different.

I am using generic names - be sure to change them.
You may need to declare the variables outside the code to transfer from one
sub to the other.
(I included a couple of extras to use if needed)

Dim col1 as long, lrw1 as long, rw1 as long, lrw2 as long, rw2 as long

Sub CheckReplaceData()

' find number of rows in data workbook
lrw1=Workbooks("wkbk1").Sheets("NewStuff").Cells(R ows.COUNT,
"A").End(xlUp).Row

' loop through NewStuff - assuming that New stuff starts on row 2 and has 8
columns of data
For rw1 = 2 to lrw1
If
WorksheetFunction.Countif(Workbooks("wkbk2").Sheet s("OldStuff").Columns(1),
_
Workbooks("wkbk1").Sheets("NewStuff").Cells(rw1,1) )= 0 Then
' your code to add a new line
else MacroCheckReplace
End If
Next
End Sub

Sub MacroCheckReplace()
' row to check and change in OldStuff
rw2 =
worksheetfunction.Match(Workbooks("wkbk1").Sheets( "NewStuff").Cells(rw1,1),
_
Workbooks("wkbk2").Sheets("OldStuff").Columns(1),0 )

' loop each column
For col1 = 2 to 8
if Workbooks("wkbk1").Sheets("NewStuff").Cells(rw1,co l1) < _
Workbooks("wkbk2").Sheets("OldStuff").Cells(rw2,co l1) then
Workbooks("wkbk2").Sheets("OldStuff").Cells(rw2,co l1) = _
Workbooks("wkbk1").Sheets("NewStuff").Cells(rw1,co l1)
Else ' put in other code if the cells are equal
End If
Next

End Sub

--
steveB

Remove "AYN" from email to respond
"lalthan" wrote in
message ...

Thanks Steve for the guidance.

Do you have a sample macro which list all those you have mentioned
above ? If so, can you please post it?

Thanks again.


--
lalthan
------------------------------------------------------------------------
lalthan's Profile:
http://www.excelforum.com/member.php...o&userid=10557
View this thread: http://www.excelforum.com/showthread...hreadid=397222

Hello,
I've tried the code. I have replaced the workbook name (master data)
and sheet name and I am getting an error with the following two codes
below:

1.If WorksheetFunction.Countif(Workbooks("Management Information
System_2005").Sheets("Raw Data").Columns(1),
_Workbooks("wkbk1").Sheets("NewStuff").Cells(rw1,1 ))= 0 Then

2.rw2
=worksheetfunction.Match(Workbooks("wkbk1").Sheets ("NewStuff").Cells(rw1,1),_Workbooks("wkbk2").Shee ts("OldStuff").Columns(1),0)


Also, if a new entry in the new data is missing in the master file,
what should be the correct code that I should use to add a new line in
the master file and put all the values in the new line


--
lalthan
------------------------------------------------------------------------
lalthan's Profile: http://www.excelforum.com/member.php...o&userid=10557
View this thread: http://www.excelforum.com/showthread...hreadid=397222

Lathan,

Your errors most likey come from excel not recognizing the workbook and
worksheet names.
And possibly not seeing some multiple lines as single lines.

If you make sure that Option Explicit are at the top of your modules - Excel
will help you find potential
problems with your code.

Also - it helps to tell us what error code/message you are getting.

1st make sure that this appears as a single line in the code. (Note the
line continuation " _" )
2nd replace wkbk1 and NewStuff with the corresponding names of the data
workbook.
And you still have wkbk2 and OldStuff that need replacing.

If WorksheetFunction.Countif(Workbooks("Management Information
System_2005").Sheets("Raw Data").Columns(1), _
Workbooks("wkbk1").Sheets("NewStuff").Cells(rw1,1) )= 0 Then
rw2
=worksheetfunction.Match(Workbooks("wkbk1").Sheets ("NewStuff").Cells(rw1,1),_Workbooks("wkbk2").Shee ts("OldStuff").Columns(1),0)
End If

To add a new line to OldStuff:

Dim lrw as Long

' find last used row in Old data and add 1 to get next empty row
lrw = Workbooks("wkbk2").Sheets("OldStuff").Cells(Rows.C OUNT,
"A").End(xlUp).Row +1

' Copy and paste new data to old stuff - copies entire row and pastes first
cell in empty row
' you could alter the code to copy a range and paste it.
Workbooks("wkbk1").Sheets("NewStuff").Rows(rw1).Co py _
Destination:= Workbooks("wkbk2").Sheets("OldStuff").Range("A" & lrw)

--
steveB

Remove "AYN" from email to respond
"lalthan" wrote in
message ...

Hello,
I've tried the code. I have replaced the workbook name (master data)
and sheet name and I am getting an error with the following two codes
below:

1.If WorksheetFunction.Countif(Workbooks("Management Information
System_2005").Sheets("Raw Data").Columns(1),
_Workbooks("wkbk1").Sheets("NewStuff").Cells(rw1,1 ))= 0 Then

2.rw2
=worksheetfunction.Match(Workbooks("wkbk1").Sheets ("NewStuff").Cells(rw1,1),_Workbooks("wkbk2").Shee ts("OldStuff").Columns(1),0)


Also, if a new entry in the new data is missing in the master file,
what should be the correct code that I should use to add a new line in
the master file and put all the values in the new line


--
lalthan
------------------------------------------------------------------------
lalthan's Profile:
http://www.excelforum.com/member.php...o&userid=10557
View this thread: http://www.excelforum.com/showthread...hreadid=397222

Hello Steve,
I've tried out the macro and it goes like this.Here the workbook wher
the datas are entered regularly is called "newdata" and the sheet i
called "NewStuff". This is where all the new datas are entered.
This workbook is to be updated to the workbook called "Managemen
Information System_2005" and the name of the sheet is "Raw Data". Ra
Data has around 46 Column Headers (Names, Codes, City, Date of Entry
Currency Amount etc) and about 3000 rows of informations consisting of
text, numbers, strings, alphanumbericals etc.

"NewStuff" sheet contains almost the same thing as "Raw Data". Ne
entries are entered here in this sheet and they are to be updated t
the Raw Data sheet at the end of the month.

I am still getting errors now. Can you please see where I am goin
wrong.



------------------------------------------------------
Sub CheckReplaceData()

' find number of rows in data workbook.
lrw1 = Workbooks("newdata").Sheets("NewStuff").Cells(Rows .Count
"A").End(xlUp).Row

'(NOTE: IN THE ABOVE CODE, I am getting a "Runtime Error 9, Subscrip
out of Range" Message)

' loop through NewStuff - assuming that New stuff starts on row 2 an
has 8
'columns of data
For rw1 = 2 To lrw1
If WorksheetFunction.CountIf(Workbooks("Management Informatio
System_2005").Sheets("Raw Data").Columns(1)
Workbooks("newdata").Sheets("NewStuff").Cells(rw1, 1)) = 0 Then
' your code to add a new line
Dim lrw As Long
' find last used row in Old data and add 1 to get next empty row
lrw = Workbooks("Management Information System_2005").Sheets("Ra
Data").Cells(Rows.Count, "A").End(xlUp).Row + 1

'Copy and paste new data to old stuff - copies entire row and paste
first
'cell in empty row
' you could alter the code to copy a range and paste it.
Workbooks("newdata").Sheets("NewStuff").Rows(rw1). Copy _
Destination:=Workbooks("Management Informatio
System_2005").Sheets("Raw Data").Range("A" & lrw)

Else: MacroCheckReplace
End If
Next
End Sub
-----------------------------------------------------------------------------
Sub MacroCheckReplace()
' row to check and change in OldStuff
rw2
WorksheetFunction.Match(Workbooks("newdata").Sheet s("NewStuff").Cells(rw1
1), Workbooks("Management Information System_2005").Sheets("Ra
Data").Columns(1), 0)

' loop each column
For col1 = 2 To 8
If Workbooks("newdata").Sheets("NewStuff").Cells(rw1, col1) < _
Workbooks("Management Information System_2005").Sheets("Ra
Data").Cells(rw2, col1) Then
Workbooks("Management Information System_2005").Sheets("Ra
Data").Cells(rw2, col1) = _
Workbooks("newdata").Sheets("NewStuff").Cells(rw1, col1)
Else ' put in other code if the cells are equal
End If
Next

End Sub

'Sub InsertRow()
'Dim Rng
'Rng = InputBox("Enter number of rows required.")
'Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
'Selection.EntireRow.Insert
'End Su

--
laltha
-----------------------------------------------------------------------
lalthan's Profile: http://www.excelforum.com/member.php...fo&userid=1055
View this thread: http://www.excelforum.com/showthread.php?threadid=39722

Lathan,

Be sure to:
Dim lrw1 as Long
The error indicates that excel doesn't recognize the workbook and/or the
worksheet name(s)
Check that the workbook name is "newdata" and the worksheet name is
"NewStuff"

And make sure excel sees it as a single line:
lrw1 = _
Workbooks("newdata").Sheets("NewStuff").Cells(Rows .Count,"A").End(xlUp).Row
--
steveB

Remove "AYN" from email to respond
"lalthan" wrote in
message ...

Hello Steve,
I've tried out the macro and it goes like this.Here the workbook where
the datas are entered regularly is called "newdata" and the sheet is
called "NewStuff". This is where all the new datas are entered.
This workbook is to be updated to the workbook called "Management
Information System_2005" and the name of the sheet is "Raw Data". Raw
Data has around 46 Column Headers (Names, Codes, City, Date of Entry,
Currency Amount etc) and about 3000 rows of informations consisting of
text, numbers, strings, alphanumbericals etc.

"NewStuff" sheet contains almost the same thing as "Raw Data". New
entries are entered here in this sheet and they are to be updated to
the Raw Data sheet at the end of the month.

I am still getting errors now. Can you please see where I am going
wrong.



------------------------------------------------------
Sub CheckReplaceData()

' find number of rows in data workbook.
lrw1 = Workbooks("newdata").Sheets("NewStuff").Cells(Rows .Count,
"A").End(xlUp).Row

'(NOTE: IN THE ABOVE CODE, I am getting a "Runtime Error 9, Subscript
out of Range" Message)

' loop through NewStuff - assuming that New stuff starts on row 2 and
has 8
'columns of data
For rw1 = 2 To lrw1
If WorksheetFunction.CountIf(Workbooks("Management Information
System_2005").Sheets("Raw Data").Columns(1),
Workbooks("newdata").Sheets("NewStuff").Cells(rw1, 1)) = 0 Then
' your code to add a new line
Dim lrw As Long
' find last used row in Old data and add 1 to get next empty row
lrw = Workbooks("Management Information System_2005").Sheets("Raw
Data").Cells(Rows.Count, "A").End(xlUp).Row + 1

'Copy and paste new data to old stuff - copies entire row and pastes
first
'cell in empty row
' you could alter the code to copy a range and paste it.
Workbooks("newdata").Sheets("NewStuff").Rows(rw1). Copy _
Destination:=Workbooks("Management Information
System_2005").Sheets("Raw Data").Range("A" & lrw)

Else: MacroCheckReplace
End If
Next
End Sub
-----------------------------------------------------------------------------
Sub MacroCheckReplace()
' row to check and change in OldStuff
rw2 =
WorksheetFunction.Match(Workbooks("newdata").Sheet s("NewStuff").Cells(rw1,
1), Workbooks("Management Information System_2005").Sheets("Raw
Data").Columns(1), 0)

' loop each column
For col1 = 2 To 8
If Workbooks("newdata").Sheets("NewStuff").Cells(rw1, col1) < _
Workbooks("Management Information System_2005").Sheets("Raw
Data").Cells(rw2, col1) Then
Workbooks("Management Information System_2005").Sheets("Raw
Data").Cells(rw2, col1) = _
Workbooks("newdata").Sheets("NewStuff").Cells(rw1, col1)
Else ' put in other code if the cells are equal
End If
Next

End Sub

'Sub InsertRow()
'Dim Rng
'Rng = InputBox("Enter number of rows required.")
'Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
'Selection.EntireRow.Insert
'End Sub


--
lalthan
------------------------------------------------------------------------
lalthan's Profile:
http://www.excelforum.com/member.php...o&userid=10557
View this thread: http://www.excelforum.com/showthread...hreadid=397222