see the symbol and the column K data  of book4 and paste the data in column K of Book5 in the same symbol

Sachin Singh
Sachin Singh used Ask the Experts™
on
see the symbol and the column K data  of book4 and paste the data in column K of Book5 in the same symbol
my both files are located in the same place i.e located in C:\Users\user\Desktop
my files name is book4 and book5
i will place the code in book5
Book4.xlsb
Book5.xlsb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...

Sub CopyColumnKFromBook4()
Dim Book4 As Workbook, Book5 As Workbook
Dim ws4 As Worksheet, ws5 As Worksheet
Dim Book4Path As String, Book4Name As String
Dim x4, x5, y(), dict
Dim i As Long

Application.ScreenUpdating = False
Set Book5 = ThisWorkbook
Set ws5 = Book5.Sheets("Sheet1")
x5 = ws5.Range("A1").CurrentRegion.Value
ReDim y(1 To UBound(x5, 1) - 1, 1 To 1)
Book4Path = Book5.Path & "\"
Book4Name = "Book4.xlsb"
Set Book4 = Workbooks.Open(Book4Path & Book4Name)
Set ws4 = Book4.Sheets("Sheet1")
x4 = ws4.Range("A1").CurrentRegion.Value
Book4.Close False

Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To UBound(x4, 1)
    dict.Item(x4(i, 2)) = x4(i, 11)
Next i

For i = 2 To UBound(x5, 1)
    If dict.exists(x5(i, 2)) Then
        y(i - 1, 1) = dict.Item(x5(i, 2))
    End If
Next i
ws5.Columns("K").ClearContents
ws5.Range("K2").Resize(UBound(y), 1).Value = y
Application.ScreenUpdating = True
MsgBox "Data from Book4 has been copied successfully.", vbInformation
End Sub

Open in new window


Click the button called "Copy Data From Book4" on Sheet1 to run the code.
Book5.xlsb

Author

Commented:
Thnx Neeraj Sir for ur Great support
Problem Solved
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Sachin! Glad to know that.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Instead of Book4 my file name is OneClick
Instead of Book5 my file name is TwoClick
So it is my request plz change the file name in the code  means modify the code according to that
 i tried to doing the same But i met with the error
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
I think you should give it another try with the following method...
Open the code just use Ctrl + H to replace Book4 with OneClick and Book5 with TwoClick. Simple, isn't it?
Do it yourself and let me know if you are not able to tweak it using the above method. :)

Author

Commented:
I tried but i am getting error after replacing the same the code 25% of code  becomes red in colour
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Okay, try this..

Sub CopyColumnKFromBook4()
Dim OneClick As Workbook, TwoClick As Workbook
Dim ws4 As Worksheet, ws5 As Worksheet
Dim OneClickPath As String, OneClickName As String
Dim x4, x5, y(), dict
Dim i As Long

Application.ScreenUpdating = False
Set TwoClick = ThisWorkbook
Set ws5 = TwoClick.Sheets("Sheet1")
x5 = ws5.Range("A1").CurrentRegion.Value
ReDim y(1 To UBound(x5, 1) - 1, 1 To 1)
OneClickPath = TwoClick.Path & "\"
OneClickName = "OneClick.xlsb"
Set OneClick = Workbooks.Open(OneClickPath & OneClickName)
Set ws4 = OneClick.Sheets("Sheet1")
x4 = ws4.Range("A1").CurrentRegion.Value
OneClick.Close False

Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To UBound(x4, 1)
    dict.Item(x4(i, 2)) = x4(i, 11)
Next i

For i = 2 To UBound(x5, 1)
    If dict.exists(x5(i, 2)) Then
        y(i - 1, 1) = dict.Item(x5(i, 2))
    End If
Next i
ws5.Columns("K").ClearContents
ws5.Range("K2").Resize(UBound(y), 1).Value = y
Application.ScreenUpdating = True
MsgBox "Data from Book4 has been copied successfully.", vbInformation
End Sub

Open in new window

Author

Commented:
Thnx Neeraj Sir for ur Great support

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial