Loop through column and update

  • Hi Experts,
    I have 2 sheets in a excel. Master and weeklyupdate. Master has 30 columns and weeklyupdate has 23 columns. The 23 columns in weeklyupdate sheet are in the sameorder as that of Master sheet. I have unique numbers in both the sheets in Column 1. For each cell in Column1 in Master sheet, I need to update the data ( 23 columns) from weeklyupdate sheet to the master sheet. Request your help.

  • Re: Loop through column and update


    Hi , Unfortunately my organization doesn't allow uploading . However, the data is as below.


    Mastersheet :


    PEC2011 John Smith 20 25 50 60 0 and so on till column X , then static data preloaded till AZ and not to be updated PEC1011 Kane 50 40 30 20 60 and so on till column X , then static data preloaded till AZ and not to be updated



    Weeklyupdate :



    PEC1011 Kane 50 30 10 20 10 and so on till column X
    PEC2011 John Smith 10 25 50 10 10 and so on till column X



    After compare and update , master sheet to look like ,


    PEC2011 John Smith 10 25 50 10 10 and so onPEC1011 Kane 50 30 10 20 10 and so on

  • Re: Loop through column and update


    Hi, Managed to get the below code to do what i needed . But , instead of copying the entire row, how can i make this to copy only the first 23 columns ? Any help ?



    Sub compareAndCopy()


    Dim lastRowE As Integer
    Dim lastRowF As Integer
    Dim lastRowM As Integer
    Dim foundTrue As Boolean



    ' stop screen from updating to speed things up
    Application.ScreenUpdating = False
    lastRowE = Sheets("Weeklyupdates").Cells(Sheets("Weeklyupdates").Rows.Count, "A").End(xlUp).Row
    lastRowF = Sheets("Master").Cells(Sheets("Master").Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRowE
    foundTrue = False
    For j = 1 To lastRowF


    If Sheets("Weeklyupdates").Cells(i, 1).Value = Sheets("Master").Cells(j, 1).Value Then
    Sheets("Weeklyupdates").Rows(i).Copy Destination:= _
    Sheets("Master").Rows(lastRowM + 1)
    lastRowM = lastRowM + 1
    Exit For
    End If
    Next j


    Next i


    ' stop screen from updating to speed things up
    Application.ScreenUpdating = True



    End Sub

  • Re: Loop through column and update


    Hi Experts,
    PFA a sample workbook and a the code that I have done so far. While the sub updates the entire row, I need only columns that are highlighted in red to updated ( basically, the used columns for a specific cell from weeklyupdates sheet)

  • Re: Loop through column and update


    Try this. :cool:


    Bruce :cool: