CODEWITHSUNDEEP
vbaexcel
Facundo Celentano
Facundo Celentano

Reputation: 23

Updating data base from an Excel workbook with another Excel workbook

I have to update an excel data base that contains data from projects. Every week I have to download the new database that my company creates, with new projects and updated data from old projects. I want to create a macro that does this (update the new information from old projects and add new projects). The project names are unique. I tried using the next code to update the data automatically, but it doesn't do anything (my data base doesn't change) and I don't know why (every project is a row and every data from the project is a column)

    Sub UpdateData()

Dim h1 As Workbook 'workbook where the data is to be pasted
Dim s1 As Worksheet
Dim h2   As Workbook 'workbook from where the data is to copied
Dim s2 As Worksheet
Dim strName  As String   'name of the source sheet/ target workbook
Dim aCell As Range, bCell As Range
    Dim SearchString As String
    Dim ExitLoop As Boolean, matchFound As Boolean

'set to the current active workbook (the source book)
Set h2 = ActiveWorkbook
Set s2 = ActiveSheet

Set h1 = Workbooks.Open("C:\Users\BAICFL\Desktop\macro prueba.xlsx")
Set s1 = h1.Worksheets("Sheet1")


s2.Activate
Dim col As Long
Dim LastRow1 As Long
    Dim row As Long
Dim i As Integer
Dim j As Integer
with s1
LastRow1 = .Range("E" & .Rows.Count).End(xlUp).Row
End With
with s2
LastRow2 = .Range("E" & .Rows.Count).End(xlUp).Row
End With


For i = 1 To LastRow1
  For j = 1 To LastoRow2


If s2.Range("E" & j).Value = s1.Range("E" & i).Value Then

s1.Range("D" & i).Value = s2.Range("D" & j).Value
s1.Range("F" & i).Value = s2.Range("F" & j).Value
s1.Range("G" & i).Value = s2.Range("G" & j).Value
s1.Range("H" & i).Value = s2.Range("H" & j).Value
s1.Range("I" & i).Value = s2.Range("I" & j).Value
s1.Range("J" & i).Value = s2.Range("J" & j).Value
s1.Range("K" & i).Value = s2.Range("K" & j).Value
s1.Range("L" & i).Value = s2.Range("L" & j).Value
s1.Range("M" & i).Value = s2.Range("M" & j).Value
s1.Range("N" & i).Value = s2.Range("N" & j).Value
s1.Range("O" & i).Value = s2.Range("O" & j).Value
s1.Range("P" & i).Value = s2.Range("P" & j).Value
s1.Range("Q" & i).Value = s2.Range("Q" & j).Value
s1.Range("R" & i).Value = s2.Range("R" & j).Value
s1.Range("S" & i).Value = s2.Range("S" & j).Value
s1.Range("T" & i).Value = s2.Range("T" & j).Value


End If
Next
Next
End Sub

Upvotes: 2

Views: 61

Answers (1)

Xabier
Xabier

Reputation: 7735

I believe the main problem with your code is that you are declaring and setting the worksheet as AcitveWorkbook and same for worksheet, and when working with more than one workbook, you should fully qualify your ranges, as you may be viewing another workbook and VBA will assume that that is the active one.

I've also did the transfer of data in a single line of code by copying a range into your destination.

You also had a typo on your second For Loop, instead of LastRow2 you had LastoRow2...

Also i and j should be declared as Long instead of integers, have a look at the code below:

Sub UpdateData()
Dim LastRow1 As Long, LastRow2 As Long, i As Long, j As Long
Dim h1 As Workbook
Dim s1 As Worksheet
Dim h2 As Workbook: Set h2 = ThisWorkbook
Dim s2 As Worksheet: Set s2 = h2.Worksheets("Sheet1")
'declare and set your workbook/worksheet amend as required

Set h1 = Workbooks.Open("C:\Users\BAICFL\Desktop\macro prueba.xlsx")
Set s1 = h1.Worksheets("Sheet1")

LastRow1 = s1.Cells(s1.Rows.Count, "E").End(xlUp).row
LastRow2 = s2.Cells(s2.Rows.Count, "E").End(xlUp).row

For i = 1 To LastRow1
    For j = 1 To LastRow2
        If s2.Range("E" & j).Value = s1.Range("E" & i).Value Then
             s1.Range("D" & i & ":T" & i).Copy s2.Range("D" & j & ":T" & j)
        End If
    Next j
Next i
End Sub

Upvotes: 1

Related Questions