Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy paste vba

0

I have added sample files plz have a look 

my file name
1.xls
leverage.xlsx
vba code will be placed in a seperate file macro.xlsm
all files are located in same place
code has to open the file and it should do the process and save and close all the file


If column B of 1.xlsx matches with column A of leverage.xlsx then copy column D of leverage.xlsx and paste it to column J of 1.xlsx

sheet name can be anything in both the files

Answer
Discuss

Discussion

Hi Avinash.
We have given you very expensive code to do a similar task a few days ago.
We did this in the spirit of this site to teach you how to do this by yourself. 
We are keen to help and assist you when you have a particular problem but in return we would like to see some effort on your part to learn and even help others from time to time. Please show us the modifications that you have made to the code you have been given and indicate which part is giving you dificulties.
Note. when comparing cells in it does not matter which one you lookup first or second. In the senario you have described here the code would be identical to that you already have if you swapped the file order and just changed the columns. The tricky part is to remove the next empty cell bit and replace it with an absolute cell location.
k1w1sm (rep: 197) Aug 27, '19 at 4:01 pm
Sub Copy_paste()
  Dim b1 As Workbook, b2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range, f As Range
  Application.ScreenUpdating = False
  Set b1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
  Set b2 = Workbooks.Open(ThisWorkbook.Path & "\Leverage.xlsb")
  Set sh1 = b1.Sheets(1)
  Set sh2 = b2.Sheets(1)
  For Each c In sh1.Range("B1", sh1.Range("B" & Rows.Count).End(xlUp))
    Set f = sh2.Range("A:A").Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
      sh1.Cells(c.Row, "J").Value = sh2.Cells(f.Row, "D")
    End If
  Next
  b1.Save
  b1.Close False
  b2.Close False
  MsgBox "Done"
End Sub



i am gettin error
run time error 1004
method range of object worksheet failed
highlighted line For Each c In sh1.Range("B1", sh1.Range("B" & Rows.Count).End(xlUp))
plz have a look my attempt is unsuccessful Sir 
avinash (rep: 10) Aug 27, '19 at 11:43 pm
Problem Solved
I will send the code in sometime
avinash (rep: 10) Aug 28, '19 at 7:07 am
Sorry Sir I understood Sir i will follow the same
avinash (rep: 10) Aug 28, '19 at 4:05 pm
Add to Discussion

Answers

0
Sub Copy_paste()
  Dim b1 As Workbook, b2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range, f As Range
  Application.ScreenUpdating = False
  Set b1 = Workbooks.Open("1.xlsx")
  Set b2 = Workbooks.Open("leverage.xlsx")
  Set sh1 = b1.Sheets(1)
  Set sh2 = b2.Sheets(1)
  For Each c In sh1.Range("B1", sh1.Range("B" & 65536).End(xlUp))
    Set f = sh2.Range("A:A").Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then
      sh1.Cells(c.Row, "J").Value = sh2.Cells(f.Row, "D")
    End If
  Next
  b1.Save
  b1.Close False
  b2.Close False
  MsgBox "Done"
End Sub
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login