Copy paste and addition by vba

[rider1234]

Vba is placed in a seperate file
open 1.xls files
open PL.xlsx file
all files are located in same path
If column E of 1.xls matches with column A of PL.xlsx and column B of PL.xlsx is blank then copy and paste the column R data of 1.xls to column B of PL.xlsx
Or
If column E of 1.xls matches with column A of PL.xlsx and column B of PL.xlsx has some data (value) then add column R data of 1.xls to column B of PL.xlsx
save the changes made to PL.xlsx
save and close all the workbooks

example
if column B of Pl.xlsx contains -5 and column R of 1.xls contains 4 then in column B of PL.xlsx the data will be -1
if column B of Pl.xlsx contains -5 and column R of 1.xls contains -6 then in column B of PL.xlsx the data will be -11
if column B of Pl.xlsx contains 5 and column R of 1.xls contains -6 then in column B of PL.xlsx the data will be -1

https://files.engineering.com/getfile.aspx?folder=72088320-5968-4cb9-82cc-2dbb25088a48&file=1.xls

https://files.engineering.com/getfile.aspx?folder=1ad25bd2-6112-4a3b-8c2f-aee9e82fdd28&file=PL.xlsx

 

[MakeItSo]

This is obviously about data, and about consolidating data.
It would probably be better to treat it as data, too.
==>Use a database, or use your Excel workbooks as data source. A SELECT / UPDATE query would be my way to go.


"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[rider1234]

but i have to do this by vba so plz guide

 

[MakeItSo]

VBA <> Copy/Paste

https://www.connectionstrings.com/excel/

Focus on the problem, not the method.

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[rider1234]

i am unable to make the vba code for the same process and i nned vba to do this process
My first last priority would be vba
so plz have a look and do needful

 

[MakeItSo]

Here is a crude example:

Sub combine()
Dim source As Workbook, target As Workbook
Dim pl As Worksheet, x1 As Worksheet
Dim i As Integer

Set source = Workbooks.Open("C:\Users\[yourprofile]\Documents\1.xlsx")
Set target = Workbooks.Open("C:\Users\[yourprofile]\Documents\PL.xlsx")

Set x1 = source.Sheets(1)
Set pl = target.Sheets(1)

For i = 1 To 500
    If x1.Cells(i, 5).Value = pl.Cells(i, 1).Value And Not IsEmpty(pl.Cells(i, 1)) Then
        pl.Cells(i, 2).Value = CInt(pl.Cells(i, 2).Value) + CInt(x1.Cells(i, 18).Value)
    End If
Next i

target.SaveAs "C:\Users\[yourprofile]\Documents\PLnew.xlsx"
target.Close
source.Close

End Sub

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[rider1234]

Sir i am new to vba i am unable to make the code

Sub combine()
Dim source As Workbook, target As Workbook
Dim pl As Worksheet, x1 As Worksheet
Dim i As Integer

Set source = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set target = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\PL.xlsx")

Set x1 = source.Sheets(1)
Set pl = target.Sheets(1)

For i = 1 To 500
If x1.Cells(i, 5).Value = pl.Cells(i, 1).Value And Not IsEmpty(pl.Cells(i, 1)) Then
pl.Cells(i, 2).Value = CInt(pl.Cells(i, 2).Value) + CInt(x1.Cells(i, 18).Value)
End If
Next i

target.SaveAs "C:\Users\WolfieeeStyle\Desktop\PLnew.xlsx"
target.Close
source.Close

End Sub



i edited some code but this code is not working plz have a look

 

[MakeItSo]

Please be a bit more specific.
What does "not working" mean? What happens, what does not?
I have tested this on
a) file PL.xlsx with identifier in column A and few scattered integer values in column B and
b) file 1.xlsx with identifier in column E and integer values in column R

it seemed to work nicely. What works differently than you expect?

P.S: you *are* aware that I have not overwritten file PL but saved the combined result as PLnew, right?

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[SkipVought]

@rider1234

You posted two previous questions that involved workbook 1.xls.

I posted a complete solution to the first of your questions and then used the same code as a template for the complete solution for the second question.

Now you have posted a third question but plead ignorance of VBA rather than trying to use and understand the working examples that you have been given.

Now you have a third question that has very similar requirements. Yet you have not raise one finger to attempt a solution on your own. No one here at Tek-Tips would expect a novice, like you, to completely solve a problem. But we do expect that novices such as YOU, would TRY on their own and indicate such in their questions, especially given two previously related examples.

You, sir, must try on your own!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

sorry Skipvought Sir but i have tried many times but i was unable to do that after that i have posted this question
i am new and i am learning and reading the vba code and samples from google and ur sites
just trying to learn but yet i am not perfect codewriter like u it will take some time sir

 

[SkipVought]

tried many times but i was unable to do that

Well then you post the code you tried, tell us what you're trying to do, and tell us what results you got.

You said NOTHING about what you tried, because it appears that you tried NOTHING!

You want someone to GIVE you something without ANY EFFORT on your part.

Now MakeItSo has given you a gift. YOU, and you alone, must take what he have given you and the two examples I gave you, and work out a solution.

Then when some change you made, fails in some way, tell us what change you made, and what results you got.

@MakeItSo, since all three of his threads have to do with 1.xls, it would be better to have the OP provide his previous working code, and work at modifying that code, with the OP doing the work, IMNSHO.

Ball's in your court, rider!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

BTW, both, and I say BOTH of your previous threads asked for solutions using a lookup in another workbook. and that's EXACTLY what you're asking for in this thread.

A person would have to be totally indifferent or severely impaired to not at least TRY to use one of those solutions that looked up a value from 1.xls in another workbook. So show us where you tried to do that.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MakeItSo]

@SkipVought: Thanks mate! Since my code is working – at least given the initial information – I couldn't contribute anything further without more info anyway.
@rider1234: This only confirms what I thought earlier was your actual problem: you seem to focus on some specific method rather than solve your actual problem. Which is really weird given you don't seem to know much about that method you are so fixed on.
Please, do yourself a favour, try to describe in as much detail as you can give, *what* you actually want/need to achieve - not *how* you wish to achieve that.

Best,
MakeItSo

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[rider1234]

Sub Code()
Dim wbk1 As Workbook
Dim wish As Worksheet
Dim wbk2 As Workbook
Dim wsh2 As Worksheet

Application.ScreenUpdating = False

Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
Set wsh1 = wbk1.Worksheets(1)

Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\PL.xlsx")
Set wsh2 = wbk2.Worksheets(1)

End If
Next
End With

Application.DisplayAlerts = False
wbk1.Close SaveChanges:=True
wbk2.Close SaveChanges:=True
Application.DisplayAlerts = True

Application.ScreenUpdating = True
End Sub






i am unable to write a code from seeing all three posts
Sir i am new it will take some time so plz help

 

[SkipVought]

That is not trying, sir.

What you posted above is similar to what you posted in one of your other threads.

I took what you posted and I modified it to show you how to obtain a value from one sheet and perform a lookup in another sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

Sub Code()
Dim wbk1 As Workbook
Dim wish As Worksheet
Dim wbk2 As Workbook
Dim wsh2 As Worksheet
Dim i As Integer

Application.ScreenUpdating = False

Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
Set wsh1 = wbk1.Worksheets(1)

Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\PL.xlsx")
Set wsh2 = wbk2.Worksheets(1)
For i = 1 To 500
If x1.Cells(i, 5).Value = pl.Cells(i, 1).Value And Not IsEmpty(pl.Cells(i, 1)) Then
pl.Cells(i, 2).Value = CInt(pl.Cells(i, 2).Value) + CInt(x1.Cells(i, 18).Value)
End If
Next i

target.SaveAs "C:\Users\[yourprofile]\Documents\PLnew.xlsx"
target.Close
source.Close

End If
Next
End With

Application.DisplayAlerts = False
wbk1.Close SaveChanges:=True
wbk2.Close SaveChanges:=True
Application.DisplayAlerts = True

Application.ScreenUpdating = True
End Sub


i am unable to make the code the reason for that is the code has opened the book by different type of code
means there are many ways to write one code so i am confused

 

[SkipVought]

Looks as if you just posted the code that MakeItSo posted. As MakeItSo stated...

Here is a crude example:

But this did not work because it has nothing to do with a lookup.

The code I previously gave you does do a lookup, as I pointed out above.

You obviously are not trying to help yourself.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

now i understood what u mean to say


Sub Code()
Dim wbk1 As Workbook
Dim wish As Worksheet
Dim wbk2 As Workbook
Dim wsh2 As Worksheet
Dim r1 As Range, vRow2 As Variant, sLookup As String
Dim wbk3 As Workbook, wsh3 As Worksheet, lRow3 As Long
Dim lLastRow As Long

Application.ScreenUpdating = False

Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
Set wsh1 = wbk1.Worksheets(1)

Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\PL.xlsx")
Set wsh2 = wbk2.Worksheets(1)
after that i dont know

 

[SkipVought]

Well you need to think about what that code did and what you want this code to do.

The first thing is a For...Next loop to get the lookup values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

Sir i am not a professional vba code writer like u
i am confused what to do with that