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
Title | # Comments | Views | Activity |
---|---|---|---|
conditional formatting | 4 | 42 | |
Excel Calculate Average - Grouped Values | 7 | 23 | |
vba autofilter in row 4 | 6 | 11 | |
Data Copy | 4 | 24 |
Join the community of 500,000 technology professionals and ask your questions.