+ Reply to Thread
Results 1 to 2 of 2

Macro to Copy Paste Values from one workbook to another if case number matches

Hybrid View

lyndalhux Macro to Copy Paste Values... 07-23-2013, 02:42 PM
lyndalhux Re: Macro to Copy Paste... 07-24-2013, 11:36 AM
  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    2

    Macro to Copy Paste Values from one workbook to another if case number matches

    I'm very new to macros and can't figure out how to do something similar to offset-match or vlookup in a macro. I have 2 workbooks; Review workbook and Master workbook. The Review workbooks are sent to me daily with the reviewer's findings. Each workbook has 1 reviewer's findings with a unique case number that was previously assigned. The Master workbook has the list of all of the case numbers in which I copy the findings to the Master according to the case number.

    I would like a macro that would look for the case number from the Review workbook (B1) on the Master workbook (column A); if the case number is found in the Master workbook then copy the data from the Review workbook (B2:B35) and paste values in the Master workbook (column U:BB.....row will vary depending on the case number). If the case number is not found, a message saying so (Not Found) will appear.

    I appreciate any help!

  2. #2
    Registered User
    Join Date
    07-23-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Macro to Copy Paste Values from one workbook to another if case number matches

    This is what I have so far but it does not work. Since I have both workbooks open, I get a message about opening the workbook again.

    Sub ReviewData()
        Dim r As Range, c As Range, cfind As Range
        Dim wbData As Workbook
        Dim wbDes As Workbook
        Dim shData As Worksheet
        Dim shDes As Worksheet
        Set wbData = Workbooks.Open("FCRT Request Form 0723.xlsm")
        Set wbDes = Workbooks.Open("Flood Review DB3.xlsm")
        Set shData = wbData.Worksheets("Review Data")
        Set shDes = wbDes.Worksheets("Data")
        
        With shDes
            Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
            For Each c In r
                Dim x
                x = c.Value
                With shData.Columns("A:A")
                    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
                        If Not cfind Is Nothing Then
                            Dim rdata As Range
                            Set rdata = Range(.Cells(cfind.Row, "B"), .Cells(cfind.Row, "AI"))
                            rdata.Copy
                        End If
                    End With
                    .Cells(c.Row, "U").PasteSpecial
                Next c
            End With
                    
        
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Open workbook, filter values, copy/paste into Active workbook.
    By niceguy21 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2013, 12:17 PM
  2. Macro running slowly, copy/paste values only from one workbook to another
    By Jeanette White in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-24-2012, 09:42 AM
  3. Macro to Copy & Paste when worksheet names matches
    By Arinp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2012, 06:54 PM
  4. Macro for WS/WB comparisons, and copy/paste two cells based on matches
    By adilley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2011, 10:46 PM
  5. [SOLVED] Macro to open workbook and copy and paste values in to orig workbo
    By Dena X in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-15-2005, 08:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1