Replace data when importing

[themluis]

Hey guys, I'm a newbie in excel vba I'm in a internship for my school and my time here is almost ending and I need to do this or I fail I'm dealing with something that I don't know nothing and don't have enough time to learn without further ado I will explain, my problem is to create and add to my existing piece of code that was made by the help of people from various excel forums and what the code needs to do is:

The main file imports weekly information form a certain files, and imagine it copies the values present in those columns to my main file and in another week file the same information is there but with some different changes. You can noticed the changes by the column "M" I will explain what are the changes in the information that is imported it's 496 it's in trial when it is good to go it changes it status to 800 and it's ready to be implemented so I need it when detects a specific value that was 496 now in 800 to change the line where it is in to the other value of the other file like so:



value from week 12: addf asdafd asds 496

value from week 24: addf asdafd asds 800




The values that are in the SourceWb.Sheets(1) are always 496 and in the SourceWb.Sheets(2) always 800, the values that are present in SourceWb.Sheets(1) eventually will pass to the SourceWb.Sheets(2) and then I need to replace the file that is present in the main file that is 496 for the 800 one, when they pass from 496 to 800 it always occurs in another file like in week 12 file is 496 in SourceWb.Sheets(1) and then imagine in week 20 file it's in the SourceWb.Sheets(2) and it's 800. So what I need to do is to then when importing replace the line where the information from week 12 SourceWb.Sheets(1) and 496 to the one from SourceWb.Sheets(2). It probably is simple but I cannot do it, If more information is needed just say it.

The
here is the code:

    Sub ImportData()
    Application.ScreenUpdating = False
    Dim Path As String, Lstrw As Long
    Dim SourceWb As Workbook
    Dim TargetWb As Workbook

    Path = "C:\Users\DZPH8SH\Desktop\Status 496 800 semana 12 2015.xls"    'Change this to your company workbook path
    Set SourceWb = Workbooks.Open(Path)
    Set TargetWb = ThisWorkbook

    Dim n As Integer, targetRow As Long
    targetRow = 3

    With SourceWb.Sheets(1)
    Lstrw = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    .Range("M1:M" & Lstrw).AutoFilter Field:=1, Criteria1:="496"
    .Application.Union(.Range("D2:D" & Lstrw), .Range("F2:F" & Lstrw), .Range("I2:I" & Lstrw), .Range("M2:M" & Lstrw), .Range("N2:N" & Lstrw)).Copy
    TargetWb.Sheets(7).Cells(TargetWb.Sheets(7).Rows.Count, "A").End(xlUp)(2).PasteSpecial xlPasteValues
    .ShowAllData
    End With

    With SourceWb.Sheets(2)
    Lstrw = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    .Application.Union(.Range("D2:D" & Lstrw), .Range("F2:F" & Lstrw), .Range("I2:I" & Lstrw), .Range("M2:M" & Lstrw), .Range("N2:N" & Lstrw)).Copy
    TargetWb.Sheets(7).Cells(TargetWb.Sheets(7).Rows.Count, "A").End(xlUp)(2).PasteSpecial xlPasteValues
    End With

    SourceWb.Close savechanges:=False
    Application.ScreenUpdating = True

thanks for any reply in advance.

I have made a cross post in this pages:

https://www.reddit.com/r/excel/comments/3d3z8l/changing_a_value_when_importing/

 

[themluis]

Forgot to mention the other cross post: excel - replace specific values vba - Stack Overflow

 

[themluis]

the time I have to do is very limited, and I cannot deliver with the speed or quality, this because of lack of time and not knowing enough...