Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.
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
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.