Solved

Copy data from different workbook

Posted on 2011-03-24
12
329 Views
Last Modified: 2012-05-11
Hi Experts,

I would like to request Experts help. Need to crosscheck data at Listing workbook (Column A) with Master Data workbook (Column A). If the same number exists in column A (Master Data WB), copy data from Column  B to D (Listing WB) at column B to D (Master Data WB) at the row where the number exist.

If the number not available, copy data from column A to D (Listing WB) as new data row at MasterData WB. After copying the data, update cell at Column E (Master Data WB) with “StoreA”. Hope Experts will help me to create this feature. Attached the workbooks for Experts perusal.



Listing.xls
MasterData.xls
0
Comment
Question by:Cartillo
  • 7
  • 4
12 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 50 total points
ID: 35212737
Make a copy of the sheet on the Listing workbook and enter this formula on A3

=IF(ISNA(MATCH(OUT!$A3,'[Copy of MasterData.xls]MasterList'!$A:$A,0)),OUT!A3,VLOOKUP(OUT!$A3,'[Copy of MasterData.xls]MasterList'!$A:$D,COLUMN(),0))

and copy it to column D and down as far as needed.

Enter this formula in column E and copy it down

=IF(ISNA(MATCH(OUT!$A3,'[Copy of MasterData.xls]MasterList'!$A:$A,0)),"","Store A")

You would have to change the file name in the formulas (in [] brackets) to whatever you have.

See attached

Saqib
Copy-of-Listing.xls
Copy-of-MasterData.xls
0
 

Author Comment

by:Cartillo
ID: 35212752
Hi ssaqibh,

Is that possible to convert this in macro?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35212758
Hi Cartillo,

Here is a code that should do what you need if you launch it from Listing workbook, assuming Master Data is open.

Sub SendToMaster()
Dim i As Long, lgFoundRow As Long
Dim shtMaster As Worksheet, shtListing As Worksheet

Application.ScreenUpdating = False

Set shtListing = ActiveSheet
Set shtMaster = Workbooks("MasterData.xls").Sheets("MasterList")

With shtListing

    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
    
        On Error Resume Next
            lgFoundRow = Application.WorksheetFunction.Match(.Cells(i, 1), shtMaster.Columns(1), 0)
        
        If Err <> 0 Then
            Err.Clear
            .Rows(i).Copy shtMaster.Cells(Rows.Count, 1).End(xlUp).Offset(1)
            shtMaster.Cells(Rows.Count, 1).End(xlUp).End(xlToRight).Offset(, 1) = "StoreA"
        Else
            .Range(.Cells(i, 2), .Cells(i, 4)).Copy shtMaster.Cells(lgFoundRow, 2)
        End If
        
    Next i
    
End With

Application.ScreenUpdating = True

End Sub

Open in new window


Thomas
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:Cartillo
ID: 35212779
Hi Thomas,

Thanks for the code.

I’m intent to save the MasterData workbook at this network folder " \\dac0q\File\ Document\Data". Can we copy the Listing data to this network folder path? and if in case the workbook was open at the time we intent to copy the data, the message box will remind the user that the data can't be copied at that point of time. Hope you will consider this request.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35212804
Try this then. It will open the workbook if it's not open and exit the sub after warning if the masterdata is read-only.

Thomas
Sub SendToMaster()
Dim i As Long, lgFoundRow As Long
Dim shtMaster As Worksheet, shtListing As Worksheet, wbkMaster as workbook

Application.ScreenUpdating = False
On Error Resume Next

Set shtListing = ActiveSheet

set wbkMaster = Workbooks("MasterData.xls")

if err<>0 then set wbkMaster =workbooks.open("\\dac0q\File\ Document\Data\MasterData.xls")

if wbkMaster.readonly then
msgbox "MasterData workbook must be opened read-only for data update. Try again later"
exit sub
end if

Set shtMaster = wbkMaster .Sheets("MasterList")


With shtListing

    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
    

            lgFoundRow = Application.WorksheetFunction.Match(.Cells(i, 1), shtMaster.Columns(1), 0)
        
        If Err <> 0 Then
            Err.Clear
            .Rows(i).Copy shtMaster.Cells(Rows.Count, 1).End(xlUp).Offset(1)
            shtMaster.Cells(Rows.Count, 1).End(xlUp).End(xlToRight).Offset(, 1) = "StoreA"
        Else
            .Range(.Cells(i, 2), .Cells(i, 4)).Copy shtMaster.Cells(lgFoundRow, 2)
        End If
        
    Next i
    
End With

Application.ScreenUpdating = True

End Sub

Open in new window

0
 

Author Comment

by:Cartillo
ID: 35213951
Hi Thomas,

Thanks for the revised code. Have checked, new data row was created at MasterData workbook even though the number is already existed at MasterList. Hope you can fix this.
0
 

Author Comment

by:Cartillo
ID: 35214061
Hi Thomas,

Sorry for the wrong update. Actually what happen was,each time I update the workbook (Master Data), row 3 from Listing workbook is copied over as new row at Master Data WB. Hope you can fix this. Can we save and closed the Masterdata WB after updating the workbook?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35215813
Sure, try this, with the addition of a save line at the end.

THomas


Sub SendToMaster()
Dim i As Long, lgFoundRow As Long
Dim shtMaster As Worksheet, shtListing As Worksheet, wbkMaster as workbook

Application.ScreenUpdating = False
On Error Resume Next

Set shtListing = ActiveSheet

set wbkMaster = Workbooks("MasterData.xls")

if err<>0 then set wbkMaster =workbooks.open("\\dac0q\File\ Document\Data\MasterData.xls")

if wbkMaster.readonly then
msgbox "MasterData workbook must be opened read-only for data update. Try again later"
exit sub
end if

Set shtMaster = wbkMaster.Sheets("MasterList")


With shtListing

    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
    

            lgFoundRow = Application.WorksheetFunction.Match(.Cells(i, 1), shtMaster.Columns(1), 0)
        
        If Err <> 0 Then
            Err.Clear
            .Rows(i).Copy shtMaster.Cells(Rows.Count, 1).End(xlUp).Offset(1)
            shtMaster.Cells(Rows.Count, 1).End(xlUp).End(xlToRight).Offset(, 1) = "StoreA"
        Else
            .Range(.Cells(i, 2), .Cells(i, 4)).Copy shtMaster.Cells(lgFoundRow, 2)
        End If
        
    Next i
    
End With

wbkmaster.close(true)

Application.ScreenUpdating = True

End Sub

Open in new window

0
 

Author Comment

by:Cartillo
ID: 35220279
Hi Thomas,

Thanks for the help. Noticed the first data row (row 3) from Listing workbook always copied at Master Data workbook (multiple entry) each update. I have attached the sample for your perusal. Please help.
MasterData.xls
0
 
LVL 39

Accepted Solution

by:
nutsch earned 450 total points
ID: 35220831
I think I get it. It seems I'm missing an err.clear in the code.

Thomas
Sub SendToMaster()
Dim i As Long, lgFoundRow As Long
Dim shtMaster As Worksheet, shtListing As Worksheet, wbkMaster as workbook

Application.ScreenUpdating = False
On Error Resume Next

Set shtListing = ActiveSheet

set wbkMaster = Workbooks("MasterData.xls")

if err<>0 then set wbkMaster =workbooks.open("\\dac0q\File\ Document\Data\MasterData.xls")

if wbkMaster.readonly then
msgbox "MasterData workbook must be opened read-only for data update. Try again later"
exit sub
end if

Set shtMaster = wbkMaster.Sheets("MasterList")

err.clear

With shtListing

    For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
    

            lgFoundRow = Application.WorksheetFunction.Match(.Cells(i, 1), shtMaster.Columns(1), 0)
        
        If Err <> 0 Then
            Err.Clear
            .Rows(i).Copy shtMaster.Cells(Rows.Count, 1).End(xlUp).Offset(1)
            shtMaster.Cells(Rows.Count, 1).End(xlUp).End(xlToRight).Offset(, 1) = "StoreA"
        Else
            .Range(.Cells(i, 2), .Cells(i, 4)).Copy shtMaster.Cells(lgFoundRow, 2)
        End If
        
    Next i
    
End With

wbkmaster.close(true)

Application.ScreenUpdating = True

End Sub

Open in new window

0
 

Author Closing Comment

by:Cartillo
ID: 35220894
Hi Thomas,

Thanks a lot for the superb solution.
0
 

Author Comment

by:Cartillo
ID: 35228482
Hi Thomas,

Hope you can help me with this Q, I'm using your solution but intent to amend it. Hope you will consider:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26913230.html
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question