copy paste by vba
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: copy paste by vba

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default copy paste by vba

    If column E matches with column C and column M has a blank cell then ignore that


    If column E of sample1.xls matches with column C of sample2.csv and column M of sample1.xls contains (-) minus sign example(-1.00,-2.00 etc) then see column O of sample1.xls and calculate the 1% of column O of sample1.xls and add the 1% of column O of sample1.xls with itself and paste the result to column L of sample2.csv


    Or


    If column E of sample1.xls matches with column C of sample2.csv and column M of sample1.xls doesnt contains (-) minus sign example(1.00,2.00 etc) then see column P of sample1.xls calculate the 1% of column P of sample1.xls and subtract 1% of column P of sample1.xls with itself and paste the result to column L of sample2.csv
    save and colse all the workbooks
    i have to do the same by vba
    sample1.xls contains headers in the first row so ignore first row
    sample2.csv doesnt contains any headers
    vba is placed in a seperate file
    all files are located in same place
    only 1 file is opened vba code placed file so we have to open the the sample1.xls file and sample2.csv file and then we have to do the process

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,952
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: copy paste by vba

    Check the file names to be sure they are correct before running this code. It is untested.

    Code:
    Sub t()
    Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range, fPath As String
    fPath = ThisWorkbook.Path & "\"
    Set wb1 = Workbooks.Open(fPath & "sample1.xls") 'Verify file name
    Set wb2 = Workbooks.Open(fPath & "sample2.csv") 'Verify file name
    Set sh1 = wb1.Sheets(1)
    Set sh2 = wb2.Sheets(1)
        For Each c In sh1.Range("E2", sh1.Cells(Rows.Count, 5).End(xlUp))
            If c.Offset(, 8) <> "" Then
            Set fn = sh2.Range("C1", sh2.Cells(Rows.Count, 3).End(xlUp)).Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    If c.Offset(, 8) < 0 Then
                        fn.Offset(, 9) = c.Offset(, 10).Value + (c.Offset(, 10) * 0.01)
                    ElseIf c.Offset(, 8) > 0 Then
                        fn.Offset(, 9) = c.Offset(, 11).Value - (c.Offset(, 11) * 0.01)
                    End If
                End If
            End If
        Next
        wb1.Close True
        wb2.Close True
    End Sub
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy paste by vba

    Thnx Jlgwhiz Sir thnx for the support but i am getting error while runing the code
    I tried to edit the code and make it workable but i was unsuccessful so plz have a relook in the vba code

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,952
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: copy paste by vba

    What is the error message and what line of code is highlighted when you click the debug button? Please be specific when describing your issues. Remember that we cannot see your worksheet so you must give accurate details in your posts.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy paste by vba

    application defined or object defined error
    while debugging
    For Each c In sh1.Range("E2", sh1.Cells(rows.Count, 3).End(xlUp))
    this line is highlighted

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,952
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: copy paste by vba

    Quote Originally Posted by sumans View Post
    application defined or object defined error
    while debugging
    For Each c In sh1.Range("E2", sh1.Cells(rows.Count, 3).End(xlUp))
    this line is highlighted
    If you look at Post #2 you will see that the 3 should be a 5. I don't know how that would get changed if you copied the code and pasted it into the code module. If you are retyping the code then it is subject to typo errors. Just change the 3 to a 5 and it should work.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy paste by vba

    yes sir it was 5 but when i ran that code i am getting error so i tried editing the code but i was unsuccessful
    so plz recheck the code i have putted the same code which u sent and i am getting error


    Error - application defined or object defined error

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,952
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: copy paste by vba

    I don't believe I can do any more without seeing the workbooks, but I will try to set up a simulation and run the code. If I find anything that needs to change I will post back here.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  9. #9
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,952
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: copy paste by vba

    I set up a simulation with two files having data in the columns as specified in Post #1 and the code being run from a third file. No errors occured during the test so I cannot resolve the problem you are having with it. I have no other suggestions to offer.
    Regards, JLG
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy paste by vba

    I am not as knowledgeful like u
    but this code will not run(will not work) as per ur code
    plz recheck this cods sir

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •