×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Copy paste and addition by vba

Copy paste and addition by vba

Copy paste and addition by vba

(OP)
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=...
https://files.engineering.com/getfile.aspx?folder=...

RE: Copy paste and addition by vba

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.

RE: Copy paste and addition by vba

(OP)
but i have to do this by vba so plz guide

RE: Copy paste and addition by vba

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.

RE: Copy paste and addition by vba

(OP)
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

RE: Copy paste and addition by vba

Here is a crude example:

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\[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.

RE: Copy paste and addition by vba

(OP)
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

RE: Copy paste and addition by vba

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.

RE: Copy paste and addition by vba

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)
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

RE: Copy paste and addition by vba

Quote (rider1234)

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

@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.

RE: Copy paste and addition by vba

(OP)
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

RE: Copy paste and addition by vba

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)
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

RE: Copy paste and addition by vba

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)
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

RE: Copy paste and addition by vba

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

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

RE: Copy paste and addition by vba

Tek-Tips is not a free coding service. I have given you code that you can adapt. I am trying to help you do that but you are unwilling to help yourself.

Therefore, that is all I am willing to do for you.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

Quote (rider1234 )

I have to do this by vba so plz guide
My first last priority would be vba
i am new to vba
i am not a professional vba code writer
i am confused what to do with that

It looks to me that either:
  • You are a student who wants to learn VBA in Excel. If so, this is not really the forum for you. People here do not ‘teach’, they rather help others who already know the subject. And if you do want to learn, your approach is wrong – you should start with something a LOT simpler to learn the basics first.
  • You were assigned a task (at work?) that is WAY above your head. If that’s the case, even if you write a working code without knowing what you wrote, it is dangerous because any changes or modifications to this code run the risk of giving you wrong outcome. And that’s not good at work, or in any other environment.


---- Andy

There is a great need for a sarcasm font.

RE: Copy paste and addition by vba

(OP)
I am a student i dont know anything about vba but seein some samples i have learned vba thats y i can write something by vba and i have already mentioned that i know
by seeing some sample and by seeing some complicated samples i will try to learn from that code
it will be a great opportunity for me to learn and see the vba code samples from a professional vba programmer sir u r seniors and i am a fresher so it will be a great guidance from u
sir plz provide the solution of the same and i promise that next time when i ask the question u will see the improvement in me

RE: Copy paste and addition by vba

Sorry. NOW is the time to begin, grasshopper.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)
i am not as smart as u tried sir but i unable to write a code but next time
I will provide u 75% of the code and i will ask for 25% of guidance from u sir

RE: Copy paste and addition by vba

I'm waiting...

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

Okay, here is your is the entire procedure that you picked to be the template.

Let me lead you, one step at a time.

So what would you need to change in this one line of For...Next code to make it do what you need to happen?

CODE

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)

With wsh1
      lLastRow = .UsedRange.Rows.Count
      For Each r1 In .Range(.Cells(2, "M"), .Cells(lLastRow, "M"))
         If r1.Value > 0 Then
            sLookup = .Cells(r1.Row, "V").Value
            vRow2 = Application.Match(sLookup, wsh2.Range("F:F"), 0)
            If IsError(vRow2) Then
               lRow3 = lRow3 + 1
               wsh3.Cells(lRow3, 1).Value = sLookup
            End If
         End If
      Next
   End With

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

   Application.ScreenUpdating = True
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)
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 & "\PL.xlsx")
Set wsh1 = wbk1.Worksheets(1)

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

With wsh1
lLastRow = .UsedRange.Rows.Count
For Each r1 In .Range(.Cells(2, "B"), .Cells(lLastRow, "B"))
If r1.Value > 0 Then
sLookup = .Cells(r1.Row, "E").Value
vRow2 = Application.Match(sLookup, wsh2.Range("A:A"), 0)
If IsError(vRow2) Then
lRow3 = lRow3 + 1
wsh3.Cells(lRow3, 1).Value = sLookup
End If
End If
Next
End With

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

Application.ScreenUpdating = True
End Sub







Am i right Sir

RE: Copy paste and addition by vba

Quote:

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

Well, you made some good progress! But I have a question. Why are you looking at each value in column B in 1.xls?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)
for copy and paste the data and calculation of the data
if it is a blank then simply copy and paste
if present then calculate and paste the result
if present then plz see the example for understanding purpose i have mentioned in this post

RE: Copy paste and addition by vba

(OP)
thnx sir

RE: Copy paste and addition by vba

(OP)
But i am unable to write a code for the same ?

RE: Copy paste and addition by vba

rider1234 - do you see how your code shows up in your posts?
Do you see Skip'd code in his posts?

Please learn how to use TGML tags - highlight the text in your post that is code and click on the icon to display your code as Skip does. Use Preview button before you Submit your post.


---- Andy

There is a great need for a sarcasm font.

RE: Copy paste and addition by vba

(OP)
Sorry Sir i understood my mistake in future it will not be repeated

RE: Copy paste and addition by vba

Please focus on the question at hand. I asked you...

Quote:

Why are you looking at each value in column B in 1.xls?
and your reply was totally unrelated.

Please answer this question.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)
that if there is a data in column B then add or subtract it depends on the data

RE: Copy paste and addition by vba

I'm sorry. If you can't understand this quite elementary issue, I cannot help you because you just do not understand some very elementary reasoning and logic.

Good bye.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

I will give you a hint. Use the solution in thread707-1795470: remove colours by vba based on conditions in excel.

Make minor changes to the statements that start with

Starting with       Purpose for change
Set wbk2 =          To open the correct workbook
For Each r1         To traverse the column with the lookup data
If r1.Value         To assure that the reference has a string value
sLookup =           To assign the proper lookup value
vRow2 =             NO CHANGE REQUIRED
If Not              NO CHANGE REQUIRED
wsh2.Rows(vRow2)    When you get to this statement, you will need additional help
 
If you can't make the very minor changes in 4 statements, then I won't provide any subsequent help. Any ordinary person with motivation, ought to be able to figure out by looking at the requirements stated in that thread, how the corresponding code is written and then applying that knowledge to this thread's requirements.

I've made it extremely easy for you to to tackle this change with several specific tips.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)

CODE -->

Sub Code()
    Dim wbk1 As Workbook
    Dim wsh1 As Worksheet
    Dim wbk2 As Workbook
    Dim wsh2 As Worksheet
    Dim r1 As Range, vRow2 As Variant, sLookup As String
    
    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)
    
    With wsh1
        For Each r1 In .Range(.Cells(2, "E"), .Cells(2, "E").End(xlDown))
                sLookup = .Cells(r1.Row, "E").Value
                vRow2 = Application.Match(sLookup, wsh2.Range("A:A"), 0)
                If r1.Value > 0 Then
                If Not IsError(vRow2) Then _
                    wsh2.Rows(vRow2).
            End If
        Next
    End With
    
    Application.DisplayAlerts = False
    wbk1.Close SaveChanges:=True
    wbk2.Close SaveChanges:=True
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
End Sub 




Plz have a look sir

RE: Copy paste and addition by vba

Yes!

CODE

Sub Code()
    Dim wbk1 As Workbook
    Dim wsh1 As Worksheet
    Dim wbk2 As Workbook
    Dim wsh2 As Worksheet
    Dim r1 As Range, vRow2 As Variant, sLookup As String
    
    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)
    
    With wsh1
        For Each r1 In .Range(.Cells(2, "E"), .Cells(2, "E").End(xlDown))
            sLookup = .Cells(r1.Row, "E").Value
            vRow2 = Application.Match(sLookup, wsh2.Range("A:A"), 0)
                
            If Not IsError(vRow2) Then 
                wsh2.Cells(vRow2, "B").Value = wsh2.Cells(vRow2, "B").Value + wsh1.Cells(r1.Row, "R").Value
            End If
        Next
    End With
    
    Application.DisplayAlerts = False
    wbk1.Close SaveChanges:=True
    wbk2.Close SaveChanges:=True
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy paste and addition by vba

(OP)
Thnx Alot Skip Sir for ur great guidance and thnx for the help Sir

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close