I wrote super redundant VBA code and it works and solve my problem. I am trying to simplify it but don't know where to start due to my limited knowledge in VBA.Basically, this code works as a vlookup function and return all required data from other sheets in the same workbook. I have multiple columns to be filled so I repeat using the same codes. But I believe there is a much more neat version rather than mine.Sub LoopTem()Dim ws As WorksheetDim ws1 As WorksheetDim ws2 As WorksheetDim ws3 As WorksheetDim ws4 As WorksheetDim ws5 As WorksheetDim ws6 As WorksheetDim ws7 As WorksheetDim i As IntegerDim r As IntegerDim s As IntegerDim t As IntegerDim u As IntegerDim v As IntegerDim w As IntegerDim lastRow As IntegerDim lastRow2 As IntegerDim lastRow3 As IntegerDim lastRow4 As IntegerDim lastRow5 As IntegerDim lastRow6 As IntegerDim lastRow7 As IntegerSet ws = Worksheets(Proactive Template)Set ws2 = Worksheets(To)Set ws3 = Worksheets(Copy)Set ws4 = Worksheets(Comment)Set ws5 = Worksheets(PAM Consolidation)Set ws6 = Worksheets(PSS Contact)Set ws7 = Worksheets(Mapping)lastRow = ws.Range(Q & Rows.Count).End(xlUp).RowlastRow2 = ws2.Range(Q & Rows.Count).End(xlUp).RowlastRow3 = ws3.Range(Q & Rows.Count).End(xlUp).RowlastRow4 = ws4.Range(A & Rows.Count).End(xlUp).RowlastRow5 = ws5.Range(A & Rows.Count).End(xlUp).RowlastRow6 = ws6.Range(A & Rows.Count).End(xlUp).RowlastRow7 = ws7.Range(L & Rows.Count).End(xlUp).RowFor i = 2 To lastRowFor r = 2 To lastRow2    If ws.Cells(i, 18) = ws2.Cells(r, 17) Then       ws.Cells(i, 20) = ws2.Cells(r, 19)    Exit For    End IfNext rNext iFor i = 2 To lastRowFor s = 2 To lastRow3    If ws.Cells(i, 18) = ws3.Cells(s, 17) Then       ws.Cells(i, 21) = ws3.Cells(s, 19)    Exit For    End IfNext sNext iFor i = 2 To lastRowFor t = 2 To lastRow4    If ws.Cells(i, 14) = ws4.Cells(t, 1) Then       ws.Cells(i, 19) = ws4.Cells(t, 2)    Exit For    End IfNext tNext iFor i = 2 To lastRowFor u = 2 To lastRow5    If ws.Cells(i, 11) = ws5.Cells(u, 1) Then       ws.Cells(i, 22) = ws5.Cells(u, 2)    Exit For    End IfNext uNext i   For i = 2 To lastRowFor v = 2 To lastRow6   If ws.Cells(i, 11) = ws6.Cells(v, 1) Then      ws.Cells(i, 23) = ws6.Cells(v, 3)      ws.Cells(i, 24) = ws6.Cells(v, 2)      ws.Cells(i, 25) = ws6.Cells(v, 4)      Exit For   End IfNext vNext iFor i = 2 To lastRowFor w = 2 To lastRow6   If ws.Cells(i, 10) = ws7.Cells(w, 12) Then      ws.Cells(i, 26) = ws7.Cells(w, 13)      Exit For   End IfNext wNext iws.Cells.WrapText = FalseEnd Sub
Conditionally copying cells from other worksheets
beginner;vba;excel
There are two different ways you could go about DRYing this up. 1) Create a subroutine that takes some parameters.I want to show you this first, because it's very important to learn this concept. Anytime you see patterns like below, you should be extracting out new subroutines or functions. For i = 2 To lastRowFor r = 2 To lastRow2    If ws.Cells(i, 18) = ws2.Cells(r, 17) Then       ws.Cells(i, 20) = ws2.Cells(r, 19)    Exit For   End IfNext rNext iFor i = 2 To lastRowFor s = 2 To lastRow3    If ws.Cells(i, 18) = ws3.Cells(s, 17) Then       ws.Cells(i, 21) = ws3.Cells(s, 19)    Exit For    End IfNext sIf you look carefully at this, you'll notice that the only difference between these loops is the source column, the destination sheet, and the destination column. Sub CopyData(souceWs As Worksheet, sourceCol As Long, destWs as Worksheet, destCol As Long)    Dim lastRow As Long, destLastRow As Long    lastRow = sourceWs.Range(Q & Rows.Count).End(xlUp).Row    destLastRow = destWs.Range(Q & Rows.Count).End(xlUp).Row    Dim i As Long, j As Long    For i = 2 To lastRow        For j = 2 To destLastRow            If destWs.Cells(i, 18) = sourceWs.Cells(r, 17) Then                destWs.Cells(i, destCol) = sourceWs.Cells(r, sourceCol)                Exit For            End If        Next j    Next iEnd SubAnd then call this subroutine from your main routine once for each sheet that needs to be processed. CopyData Worksheets(To), 19, ws, 20CopyData Worksheets(Copy), 19, ws, 21'etcHowever, this would be terribly slow if there is a large amount of data to process. It would loop through the entire destination once for every call to CopyData. 2) Move everything inside of the outer loop.There's no sense in looping from 2 To destLastRow over and over again. The code gets DRYed up a good bit and sees a performance boost by moving the logic inside. For i = 2 To destLastRow    sourceWs = Worksheets(To)    lastRow = sourceWs.Range(Q & Rows.Count).End(xlUp).Row    For j = lastRow        'do stuff    Next j    sourceWs = Worksheets(Blah)    lastRow = sourceWs.Range(Q & Rows.Count).End(xlUp).Row    For j = lastRow        'do stuff    Next j    'etcNext iAt this point there's still some duplication, so you could use the concepts from #1 to extract the inner loop to its own subroutine. Some other things you could do better:The inside of each For block should be one indentation level (1 tab or four spaces) deeper. It's easier to follow. I know a lot of people innocently recommend not reusing variable names, but sometimes it's cleaner to do so. Your inner loop counter was way up past r in the alphabet. Note how I reused j above once I was done with the previous loop. It just makes for less to remember. Just make sure that you're actually done with these generic variables before reusing them. Don't number variables. If you catch yourself numbering variables, do exactly what you did =;)- Stop and look for a way to clean it up with a loop, subroutine, function, etc. If you just can't avoid it, give them meaningful names like sourceSheet and destSheet. 