Hi Everyone,
I have 2 worksheets
Sheet: "UniqueCodesInfo" with distinct values in column B (Output)
Sheet: "Sheet1" with a large data (Source Sheet)
What I am trying to do...
type VlookUp function in Cell D2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column B
type VlookUp function in Cell E2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column C
type VlookUp function in Cell F2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column G......
And so on until Column W UniqueCodesInfo.
The code below is only for 1 column because Code is Exremely slow. and I run it one at the time.
I would really appreciate your support with this.
----------------------------------------------------------------------------
Sub VlookUp_D()
Application.ScreenUpdating = False
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("UniqueCodesInfo")
'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
With outputSheet
'Determine last row in col B
OutputLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
'Apply our formula
.Range("D2:D" & OutputLastRow).Formula = "=VLOOKUP(b2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
I have 2 worksheets
Sheet: "UniqueCodesInfo" with distinct values in column B (Output)
Sheet: "Sheet1" with a large data (Source Sheet)
What I am trying to do...
type VlookUp function in Cell D2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column B
type VlookUp function in Cell E2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column C
type VlookUp function in Cell F2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column G......
And so on until Column W UniqueCodesInfo.
The code below is only for 1 column because Code is Exremely slow. and I run it one at the time.
I would really appreciate your support with this.
----------------------------------------------------------------------------
Sub VlookUp_D()
Application.ScreenUpdating = False
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("UniqueCodesInfo")
'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
With outputSheet
'Determine last row in col B
OutputLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
'Apply our formula
.Range("D2:D" & OutputLastRow).Formula = "=VLOOKUP(b2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub