Topic Closed
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.)
Topic closed
0
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 ThenNext step is to check if Column C/D/etc. has a value:
If wsh2.Range("C1").Value <> "" ThenDo 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.
Discussion
.I walso have a same problem
style36 (rep: 24) 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 subThnx 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 (rep: 10) Aug 17, '19 at 10:45 pm
This Problem is UnSolved Sir so plz have a look Sir
avinash (rep: 10) 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: 1989) 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 (rep: 10) 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: 1989) 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)
i am attaching the sample file in the post plz have a look at the starting post(my 1st post of this thread)
avinash (rep: 10) Aug 22, '19 at 2:32 pm
Any Doubts Any question then plz let me know
avinash (rep: 10) Aug 24, '19 at 11:25 am