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

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
Sachin SinghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sachin SinghAuthor Commented:
Thnx Neeraj Sir for ur Great support
Problem Solved
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Sachin! Glad to know that.
0
Determine the Perfect Price for Your IT Services

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

Sachin SinghAuthor 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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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. :)
0
Sachin SinghAuthor Commented:
I tried but i am getting error after replacing the same the code 25% of code  becomes red in colour
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
Sachin SinghAuthor Commented:
Thnx Neeraj Sir for ur Great support
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.