Excel VBA To Refresh Data From Different Sheet

[jdphilippe]

I posted this and didn't get any response so I am reposting and hoping to get a response.

I have an excel workbook with a worksheet called Active and a worksheet called Webcycle. Each row is a unique entry. I have columns B:O I want to have it refresh all the fields from each cell by doing a VLOOKUP on value in Cell B to worksheet Webcycle and if there isn't a value from Cell B to match then copy the row into Active sheet. So to explain more in the sheet called Webcycle I will have either NEW data I would like to be copied to Active Sheet or data I would like to refresh on Active Sheet. Formatting between the two sheets are the exact same as far as headers and stuff. Cell B would have the Document Number I would like to use to determine if its going to refresh the data from Sheet Webcycle or copy the data to Active Sheet. If its copying new data from Webcycle Sheet to Active Sheet then copy the new data at the bottom after the last row. If its refreshing the data then just copy it over the existing cells.

I'm hoping someone can help me and give me a code to make that happen. Let me know if you have any additional questions. I would greatly appreciate the assistance.

 

[frank_AL]

Re: Excel VBA To Refresh Data From Different Sheet Help

I can create a solution for you.

 

[Fluff]

Re: Excel VBA To Refresh Data From Different Sheet Help

Cross posted https://www.excelforum.com/excel-pr...vba-to-refresh-data-from-different-sheet.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[frank_AL]

Re: Excel VBA To Refresh Data From Different Sheet Help

Give this code a try.

Option Explicit


Sub RecordCompare()


Dim wsactive As Worksheet
Dim wsweb As Worksheet
Dim valexists As Variant
Dim i As Long
Dim lr As Long
Dim nr As Long


Set wsactive = Worksheets("Active")
Set wsweb = Worksheets("Webcycle")


lr = wsweb.Cells(Rows.Count, "B").End(xlUp).Row
ReDim actarray(lr - 1)
actarray = wsactive.Range("B2:B" & lr).Value
nr = wsactive.Cells(Rows.Count, "B").End(xlUp).Row + 1


For i = 2 To lr
'    valexists = WorksheetFunction.Match(Cells(i, "B"), actarray, 0)
    If IsNumeric(Application.Match(Cells(i, "B"), actarray, 0)) Then
        valexists = WorksheetFunction.Match(Cells(i, "B"), actarray, 0)
        wsactive.Range("B" & valexists & ":O" & valexists).Value _
        = wsweb.Range("B" & i & ":O" & i).Value
    Else
        wsactive.Range("B" & nr & ":O" & nr).Value _
        = wsweb.Range("B" & i & ":O" & i).Value
        nr = nr + 1
    End If
Next i


End Sub

 

[jdphilippe]

Re: Excel VBA To Refresh Data From Different Sheet Help

Thanks for the help. I tried the code and it looks like it did refresh the information correctly from what I could see. I would have to really look at the pre and post macro file to verify. However, when copying the new rows it does not seem to be copying it into the next available row. For example, records 1and 20 were suppose to copy to Active sheet, it does copy correctly however it leaves 19 blank rows in-between instead of having each line being copied to the next line. Can you tweak the code some?

Give this code a try.

Option Explicit


Sub RecordCompare()


Dim wsactive As Worksheet
Dim wsweb As Worksheet
Dim valexists As Variant
Dim i As Long
Dim lr As Long
Dim nr As Long


Set wsactive = Worksheets("Active")
Set wsweb = Worksheets("Webcycle")


lr = wsweb.Cells(Rows.Count, "B").End(xlUp).Row
ReDim actarray(lr - 1)
actarray = wsactive.Range("B2:B" & lr).Value
nr = wsactive.Cells(Rows.Count, "B").End(xlUp).Row + 1


For i = 2 To lr
'    valexists = WorksheetFunction.Match(Cells(i, "B"), actarray, 0)
    If IsNumeric(Application.Match(Cells(i, "B"), actarray, 0)) Then
        valexists = WorksheetFunction.Match(Cells(i, "B"), actarray, 0)
        wsactive.Range("B" & valexists & ":O" & valexists).Value _
        = wsweb.Range("B" & i & ":O" & i).Value
    Else
        wsactive.Range("B" & nr & ":O" & nr).Value _
        = wsweb.Range("B" & i & ":O" & i).Value
        nr = nr + 1
    End If
Next i


End Sub

 

[frank_AL]

Re: Excel VBA To Refresh Data From Different Sheet Help

I moved the line of code that detects the last row + 1 which is used to determine where to copy the record to. Give this a try.

Option Explicit


Sub RecordCompare()


Dim wsactive As Worksheet
Dim wsweb As Worksheet
Dim valexists As Variant
Dim i As Long
Dim lr As Long
Dim nr As Long


Set wsactive = Worksheets("Active")
Set wsweb = Worksheets("Webcycle")


lr = wsweb.Cells(Rows.Count, "B").End(xlUp).Row
ReDim actarray(lr - 1)
actarray = wsactive.Range("B2:B" & lr).Value


For i = 2 To lr
'    valexists = WorksheetFunction.Match(Cells(i, "B"), actarray, 0)
    If IsNumeric(Application.Match(Cells(i, "B"), actarray, 0)) Then
        valexists = WorksheetFunction.Match(Cells(i, "B"), actarray, 0)
        wsactive.Range("B" & valexists & ":O" & valexists).Value _
        = wsweb.Range("B" & i & ":O" & i).Value
    Else
        nr = wsactive.Cells(Rows.Count, "B").End(xlUp).Row + 1
        wsactive.Range("B" & nr & ":O" & nr).Value _
        = wsweb.Range("B" & i & ":O" & i).Value
    End If
Next i


End Sub