Data import and sort

[mplees]

Hi,

Hope that someone can help out with this, as I am so out of touch with Excel. I need to manipulate some client supplied data and save it in a new workbook. The source data is generated from the Sage accounting program, and saved as two separate workbooks. The formatting of the source is messy, but at least it is consistent in layout and file naming.

The two source workbooks are called 'Purchase Order per Project.xls' and 'Committed Cost.xls'; both workbooks only have a single sheet and are in Excel 97-2003 format, contain no formulae only data, and the process I need to follow is:

• Import all the data from the purchase order workbook
• Import the matching data from the committed cost workbook (matching data is where there is an entry with the same project number)
• Paste the data into a new workbook (or sheet within the same workbook), with the data ordered by project number (i.e Purchase order project 1 data, Committed cost project 1 data, Purchase order project 2 data and so on)

As the sheets are too large to effectively show on the forum, and as it's very difficult to visualise what I mean, I've zipped 3 files (total size about 68kb) which can be downloaded here:

http://www.mediafire.com/?fp8pot0l3bykihs

The two source data workbooks are there, along with a 3rd workbook that shows what the merged/imported data should look like. Can anyone offer some assistance with this? It would really be appreciated.

Regards

Mark

 

[cgcamal]

Hi mplees,

Try with this macro containing this book Merge_Purchase_and_Commited.xls.

In order to get working for you, you only have to change the path to save the resulting workbook.

Download the attached Workbook and at the end of the macro, change the part highlighted in red with
your real path, save it and run it clicking the button.

    ActiveWorkbook.SaveAs Filename:= _
        "[COLOR=Red][B]C:\Documents and Settings\YourUser\YourPath[/B][/COLOR]\Purchase Order  and Commited Cost per Project_" & DtHr & ".xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

When you run the book containing the macro "Merge_Purchase_and_Commited.xls" Both books "Purchase Order per Project.xls"
and "Committed Cost.xls" must be opened. If not you'll receive a message saying that is needed to open them.

For reference the macro inside the book is:

Sub Import_Projects()
'29-04-2011 by César C
'Macro to merge Purchase Order per Project.xls and Committed Cost.xls
Dim wBook1 As Workbook
Dim wBook2 As Workbook

Application.ScreenUpdating = False
On Error Resume Next
Set wBook1 = Workbooks("Purchase Order per Project.xls")
Set wBook2 = Workbooks("Committed Cost.xls")

        If wBook1 Is Nothing And wBook2 Is Nothing Then
            MsgBox "<<Purchase Order per Project.xls>> and <<Committed Cost.xls>> are not open", vbCritical
            MsgBox "Open both books and try again", vbInformation
            Set wBook1 = Nothing
            Set wBook2 = Nothing
            GoTo Err:
        ElseIf wBook1 Is Nothing Then
            MsgBox "<<Purchase Order per Project.xls>> is not open", vbCritical
            MsgBox "Open <<Purchase Order per Project.xls>> and try again", vbInformation
            Set wBook1 = Nothing
            GoTo Err:
        ElseIf wBook2 Is Nothing Then
            MsgBox "<<Committed Cost.xls>> is not open", vbCritical
            MsgBox "Open <<Committed Cost.xls>> and try again", vbInformation
            Set wBook1 = Nothing
            GoTo Err:
        End If

  Windows("Committed Cost.xls").Activate
    With Sheets("Committed Costs Per Project")

    ActiveSheet.Range("H:H").AutoFilter Field:=1, Criteria1:="Title"
    
    TtN = Application.WorksheetFunction.CountIf(Range("H:H"), "Title")
    TtFr = Range("H1").End(xlDown).Row
    TtLr = Range("H65000").End(xlUp).Row

    Set Rng = Range("H" & TtFr & ":H" & TtLr).SpecialCells(xlCellTypeVisible)

ActiveSheet.AutoFilterMode = False

CurrPNr = 1
For Each t In Rng
Count = Count + 1
If Count < TtN Then
    PjtLr = Range("H" & t.Row).End(xlDown).Row - 1
Else
    PjtLr = Cells(Cells.Rows.Count, "O").End(xlUp).Row
End If
    PjtNbr = Range("E" & t.Row)
    Range("A" & t.Row & ":T" & PjtLr).Copy
    
     Windows("Purchase Order per Project.xls").Activate
        With Sheets("Purchase Orders Received Per P")
    
 CurrPNr = Columns("E:G").Find(What:=PjtNbr, After:=Range("E" & CurrPNr), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Row
        
If Count < TtN Then
        CurrPNLr = Range("E" & CurrPNr).End(xlDown).Row
Else
         CurrPNLr = Cells(Cells.Rows.Count, "N").End(xlUp).Row + 1
End If
        
        Range("A" & CurrPNLr).Insert Shift:=xlDown
    
        Windows("Committed Cost.xls").Activate
        End With
Next
End With
Application.CutCopyMode = False

     Windows("Purchase Order per Project.xls").Activate

    Sheets("Purchase Orders Received Per P").Copy
    Sheets("Purchase Orders Received Per P").Name = "Purchase and Commited Prjt"
    
DtHr = Format(Now, "dd-mm-yyyy_hhmmss")
    
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\YourUser\YourPath\Purchase Order  and Commited Cost per Project_" & DtHr & ".xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

    Windows("Purchase Order per Project.xls").Close False
    Windows("Committed Cost.xls").Close False
Err:
Application.ScreenUpdating = True
End Sub<committed cost.xls=""><committed cost.xls=""><committed cost.xls="">

Hope it helps.

Best regards</committed></committed></committed>

 

[mplees]

Hola Cesar!

If I can dredge up enough Spanish - muchisimas gracias, this seems to do just what I need - thanks so much for your help.

saludos

Mark

 

[cgcamal]

Hey Mark,

Nice to know that works for you!.

Regards