Your question, your audience. Choose who sees your identity—and your question—with question security.
Sub macro()
Set FirstLine = Range(Range("B1"), Cells(1, Columns.Count).End(xlToLeft))
FirstLine.Offset(1).Resize(Rows.Count - 1).Delete
LastRow = Range("A" & Rows.Count).End(xlUp).Row
FirstLine.Resize(LastRow).FillDown
End Sub
With Range("A2", Range("A" & Rows.COunt).End(xlUp)).Offset(,1)
.Formula = "=IF(A2="""", """", VLOOKUP(A2, Sheet2!A1:F20000, 6,0))
.Value = .Value
End With
With Sheets("Sheet1")
With .Range("A2", .Range("A" & Rows.COunt).End(xlUp)).Offset(,1)
.Formula = "=IF(A2="""", """", VLOOKUP(A2, Sheet2!A1:F20000, 6,0))
.Value = .Value
End With
End With
That will apply the code to Sheet1 no matter which sheet is active.Dim arrRanges As Variant
Dim arrCols As Variant
Dim I As Long
arrRanges = Array("'HC002-SiteCode'!A:B", "'HC003-DomainorWrkGroup'!A:B", "'HC004-LastLogonDomain'!A:B", "'HC006-ChassisType'!A:B", "'HC008-OS_SP'!A:C")
arrCols = Array(2, 2, 2, 2, 3)
For I = LBound(arrRanges) To UBound(arrRanges)
With Sheets("Sheet1")
With .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Offset(, I + 1)
.Formula = "=VLOOKUP(A2," & arrRanges(I) & "," & arrCols(I) & ",0)"
' .Value = .Value
End With
End With
Next I
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.