Updating a database with macro

[lalthan]

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.

[STEVE BELL]

Lalthan,

You can do this with looping through the datasheet.
And while you are looping you can determine if each cell already exists in
the master sheet.

To determine if the employee is already on the mastersheet use something
like
worksheetfunction.countif(MasterWorkSheet-Column A, employee number) >
0)
if greater than 0 - use a match function to find the row. Than compare
each cell in the row in
the master and the row in the datasheet. If not equal, replace the
value in the master.

if countif > 1 than you'll need to do something about the duplicate.

If countif = 0 than you need to create a new entry in the master worksheet.

You'll have a lot of looping and if than statements - but if not all values
need changing, it should go fairly quickly.

See if this will get you started.

Post back and let us know how it is going and if you need any further help.

--
steveB

Remove "AYN" from email to respond
"lalthan" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[lalthan]

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.

[STEVE BELL]

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(Rows.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").Sheets("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,col1) <> _
Workbooks("wkbk2").Sheets("OldStuff").Cells(rw2,col1) then
Workbooks("wkbk2").Sheets("OldStuff").Cells(rw2,col1) = _
Workbooks("wkbk1").Sheets("NewStuff").Cells(rw1,col1)
Else ' put in other code if the cells are equal
End If
Next

End Sub

--
steveB

Remove "AYN" from email to respond
"lalthan" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[lalthan]

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").Sheets("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

[STEVE BELL]

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").Sheets("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.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("wkbk1").Sheets("NewStuff").Rows(rw1).Copy _
Destination:= Workbooks("wkbk2").Sheets("OldStuff").Range("A" & lrw)

--
steveB

Remove "AYN" from email to respond
"lalthan" <[email protected]> wrote in
message news:[email protected]...
>
> 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").Sheets("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
>

[lalthan]

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").Sheets("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

[STEVE BELL]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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").Sheets("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
>