Vba code guide

0

my files
target1.xlsx
target2.xlsx
macro.xlsm
If column E of target1.xlsx matches with column A of target2.xlsx then copy and paste the column R data of target1.xlsx to target2.xlsx from column C(if column C has data then column D and if column D has then from column E and so on...)

my all files are located in same 

Sub Mysub
 
   dim wbk1 as workbook
   dim wbk2 as workbook
 
   Application.ScreenUpdating = False
 
   Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\target1.xlsx")
   Set wsh1 = wbk1.Worksheets(1)
 
   Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\target2.xlsx")
   Set wsh2 = wbk2.Worksheets(1)
 
 
   If wbk1.[my logic] then
 
   wbk2.column(my column).value - wbk1.(my column).value
 
end if
 
   Application.DisplayAlerts = False
   wbk1.Close SaveChanges:=True
   wbk2.Close SaveChanges:=True
   Application.DisplayAlerts = True
 
   Application.ScreenUpdating = True
 
end sub
Answer
Discuss

Answers

0

It looks like you are doing pretty good here and got the hard stuff finished (referencing the other workbooks and closing them)

To help with your logic, you could try something like this:

If wsh1.Range("E1").Value = wsh2.Range("A1").Value Then

Next step is to check if Column C/D/etc. has a value:

If wsh2.Range("C1").Value <> "" Then

Do this for each column you want to check.

Then copy/paste:

wsh1.Range("E1").Copy wsh2.Range("C1")

You will need to figure out which row to paste the data into, which is done depending on how you want the macro to copy the data (overwritting the previous entry or not), but this should give you the framework that you need to finish your macro.

Discuss

Discussion

.I walso have a same problem
style36 (rep: 8) Aug 17, '19 at 10:31 pm
Sub Mysub
 
   dim wbk1 as workbook
   dim wbk2 as workbook
 
   Application.ScreenUpdating = False
 
   Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\target1.xlsx")
   Set wsh1 = wbk1.Worksheets(1)
 
   Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\target2.xlsx")
   Set wsh2 = wbk2.Worksheets(1)
 
 
   If wsh1.Range("E1").Value = wsh2.Range("A1").Value Then
   If wsh2.Range("C1").Value <> "" Then
   wsh1.Range("E1").Copy wsh2.Range("C1")
 
end if
 
   Application.DisplayAlerts = False
   wbk1.Close SaveChanges:=True
   wbk2.Close SaveChanges:=True
   Application.DisplayAlerts = True
 
   Application.ScreenUpdating = True
 
end sub


Thnx for the support Sir
But i  have putted this code but the code is not working i think that i made mistake while writing the code so plz have a look and help me out Sir
avinash Aug 17, '19 at 10:45 pm
This Problem is UnSolved Sir  so plz have a look Sir
avinash Aug 20, '19 at 11:18 am
What exactly is not working? It looks like you should put a second "End If" after the one you already have in there.
don (rep: 1705) Aug 20, '19 at 2:34 pm
Sub Mysub()
 
   Dim wbk1 As Workbook
   Dim wbk2 As Workbook
 
   Application.ScreenUpdating = False
 
   Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\ap.xls")
   Set wsh1 = wbk1.Worksheets(1)
 
   Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\PL.xlsx")
   Set wsh2 = wbk2.Worksheets(1)
 
 
   If wsh1.Range("E1").Value = wsh2.Range("A1").Value Then
   If wsh2.Range("C1").Value <> "" Then
   wsh1.Range("E1").Copy wsh2.Range("C1")
 
End If
End If
 
   Application.DisplayAlerts = False
   wbk1.Close SaveChanges:=True
   wbk2.Close SaveChanges:=True
   Application.DisplayAlerts = True
 
   Application.ScreenUpdating = True
 
End Sub



I have putted end if plz have a Relook sir after putting end if the code is not working 
avinash Aug 20, '19 at 10:49 pm
Where is the error happening? Give me more information? And, did you try it after you made the changes?
don (rep: 1705) Aug 22, '19 at 12:30 am
It doesnt copy and paste the data
i am attaching the sample file in the post plz have a look at the starting post(my 1st post of this thread)
avinash Aug 22, '19 at 2:32 pm
Any Doubts Any question then plz let me know
avinash Aug 24, '19 at 11:25 am
Add to Discussion

Answer the Question

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