Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Macro to copy and paste special values

    I have names in Col A and values in Col D in a workbook called Vat account balances (source workbook)

    I would like a macro to copy the values in the source workbook to Col E in the destination workbook (paste special values) where the name in Col D (destination workbook) matches the name in Col A (source workbook)

    Eg if name in A3 = Br1 and the value is 240,506.36 (source workbook) and D67 = BR1 , then 240,506.36 must be paste valued special in E67

    I have also posted on Mr Excel.com

    http://www.mrexcel.com/forum/excel-q...-name-col.html

    Your assistance in resolving this is most appreciated
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    I have tried to write code to do this but error 13 -type mismatch and the following code is highlighted

    Code:
     Set sourceBook = Workbooks("Vat account Balances.xls").Sheets(1)
    
    Sub getDataFromSource()
    Dim destBook As Workbook, sourceBook As Workbook, lastRow&
    Set destBook = ThisWorkbook
    Set sourceBook = Workbooks("Vat account Balances.xls").Sheets(1)
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        For Each r In Range("a6:a" & lastRow)
            With destBook.ActiveSheet.Columns("e")
                Set c = .Find(r.Value, , , 1)
                    If Not c Is Nothing Then
                        c.Offset(, 1).Value = r.Offset(, 3).Value
                    End If
            End With
        Next
    
    Set deskbook = Nothing
    Set sourceBook = Nothing
    End Sub

    Your assistance in resolving this is most apreciated

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    sourceworkbook is defined as a workbook.

    Workbooks("Vat account Balances.xls").Sheets(1) is a worksheet

    Perhaps it should be:
    Set sourceBook = Workbooks("Vat account Balances.xls").

    or
    Dim sourcebook as worksheet
    Set sourceBook = Workbooks("Vat account Balances.xls").Sheets(1)

    I can't tell from the code since you don't use the variable in the code...

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help. I changed the code from Workbooks("Vat account Balances.xls").Sheets(1) to Set sourceBook = Workbooks("Vat account Balances.xls"). and macro now runs

    However,where the names in Col A (source workbook) matches the names in Col F (destination workbook) , the values in Col D are not copied to Col G in the destination workbook

    Please test & amend

    Howard

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Steve

    I managed to resolve issue

    Code:
     Sub getDataFromSource()
    Dim destBook As Workbook, sourceBook As Workbook, lastRow&
    Set destBook = ThisWorkbook
    Set sourceBook = Workbooks("Vat account Balances.xls") 'change as needed
    With sourceBook.Sheets(1)
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For Each r In .Range("a6:a" & lastRow)
            With destBook.ActiveSheet.Columns("f")
                Set c = .Find(r.Value, , , 1)
                    If Not c Is Nothing Then
                        c.Offset(, 1).Value = r.Offset(, 3).Value
                    End If
            End With
        Next
    End With
    Set deskbook = Nothing
    Set sourceBook = Nothing
    End Sub

Posting Permissions

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