Really stuck on using VB to sort Raw data in standard layout

[Dave01]

Hi,

Im really stuck and getting nowhere fast, mainly because I dont know VB. Im pretty good when it comes to excel formulas, but it looks like what I am tring to do will only work through VB, and it isnt a really complicated issue either, just I have no idea what I am doing when it comes to VB.

So this is what Im tring to do, We download a payment run from SAP into excel, but even though it is in columns its always messy to work with, a macro, which I have recorded is working and does sort out the data into a new tab. The problem is this only works for 1 entity, (Belgium) but we have many entites, France, Spain etc, which always seems to come out in a different yet similar layout, it maybe a few columns out, or include extra columns I dont want, this means that my macro will not work, unless the layout is exactly the same as the the 1st entity which the Macro was recorded, (Belgium)

So what I will end up with is 1 spreadsheet, where I drop the data into a tab called Downloaded Raw Proposal, do a new Macro / VB code where it will look for the columns it needs, ie search by headers, then copy the entire column, inc all the data and paste it into the relevent column as shown below.

[TABLE="width: 0"]
<tbody>[TR]
[TD]Cell Ref needed
[/TD]
[TD]Description
[/TD]
[/TR]
[TR]
[TD]D5
[/TD]
[TD]Payment
[/TD]
[/TR]
[TR]
[TD]F6
[/TD]
[TD]CoCd
[/TD]
[/TR]
[TR]
[TD]I6
[/TD]
[TD]DocumentNo
[/TD]
[/TR]
[TR]
[TD]L6
[/TD]
[TD]Type
[/TD]
[/TR]
[TR]
[TD]M6
[/TD]
[TD]Doc Date
[/TD]
[/TR]
[TR]
[TD]O6
[/TD]
[TD]Blind Date
[/TD]
[/TR]
[TR]
[TD]R6
[/TD]
[TD]PayT
[/TD]
[/TR]
[TR]
[TD]T6
[/TD]
[TD]FC gross amount
[/TD]
[/TR]
[TR]
[TD]U6
[/TD]
[TD] Tot.ded.in FC
[/TD]
[/TR]
[TR]
[TD]V6
[/TD]
[TD]Net amnt. in FC
[/TD]
[/TR]
[TR]
[TD]W6
[/TD]
[TD]Crcy
[/TD]
[/TR]
[TR]
[TD]Y6
[/TD]
[TD]Err
[/TD]
[/TR]
</tbody>[/TABLE]


I have attached two pictures of the correct layout and the wrong layout, the columns and data must always be in the same layout as the correct picture, however these layouts except the correct one always change per entity, so they might also change on the next download, or if someone else runs them.

We and our IT \ SAP Consultants have been unable to change the layouts in SAP for the pay proposals, which is why we need this template file.

In the attached spreadsheet the Tab named New Raw data dump, contains a report which is the wrong layout,

The yellow tab - Download Raw Proposal - is where the Macro (red tab) - Run Report picks up the data and the blue tab is where is put in a more user friendly way.


I hope I have explained it well.

Help would be highly gratefully received.

Hi,

 

[Dave01]

cant seem to attach spreadsheets or pictures

 

[mumps]

This forum doesn't allow file attachments. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets.

 

[Dave01]

https://www.dropbox.com/s/glhlu6rzl...um SAP PAY DOWNLOADED RAW DATA FILE.xlsx?dl=0
https://www.dropbox.com/s/b14dnp10hp4ukz8/V2.Test.Payment Proposal Organised Template.xlsm?dl=0

https://www.dropbox.com/s/citsp801tn91biv/Wrong LayoutJPG.JPG?dl=0
https://www.dropbox.com/s/cn4cyubif5gtpvy/correct layout.JPG?dl=0

 

[Dave01]

Thanks - hopefully Ive attached the spreadsheets and pictures now

 

[mumps]

I need some clarification:
When you get the data, it looks like sheet "New Raw data dump". You want to copy that sheet to sheet "1) Download RAW Proposal" and then copy only the columns Payment, CoCd, DocumentNo, Type, Doc Date, Blind Date, PayT, FC gross amount, Tot.ded.in FC, Net amnt. in FC, Crcy, and Err and paste them in sheet "3) Pay Proposal Report". If this is correct, the "3) Pay Proposal Report" sheet has only 7 columns and you want to copy 12 columns. Please clarify. Also, is it necessary to copy all the data from sheet "New Raw data dump" to sheet "1) Download RAW Proposal" before copying the columns? Why not just copy them directly from the "New Raw data dump" sheet?

 

[Dave01]

I need some clarification:
When you get the data, it looks like sheet "New Raw data dump". You want to copy that sheet to sheet "1) Download RAW Proposal" and then copy only the columns Payment, CoCd, DocumentNo, Type, Doc Date, Blind Date, PayT, FC gross amount, Tot.ded.in FC, Net amnt. in FC, Crcy, and Err and paste them in sheet "3) Pay Proposal Report". If this is correct, the "3) Pay Proposal Report" sheet has only 7 columns and you want to copy 12 columns. Please clarify. Also, is it necessary to copy all the data from sheet "New Raw data dump" to sheet "1) Download RAW Proposal" before copying the columns? Why not just copy them directly from the "New Raw data dump" sheet?

Hi Thanks for the reply,

so how it was originally was originally was 4 tabs - Download Raw Proposal, Run Repot and pay Proposal. The run report is where he macro button was stored. The process is,
STEP1. Run a SAP Report copy and paste the worksheet into the Download Raw Proposal tab. Step 2 goto Run Report Tab click on macro. The macro looks into the data on Download Raw Proposal tab copies all the data to the Pay Proposal Report, deletes the columns not needed, so you end up with a very tidy organised report. It then looks up the error code via a Vlookup on the lookups tab copies the formula to the first cell, and returns in text what the error code means. Then it will wipe all the data in the Download Raw Proposal tab, ready for the next one. thats all it does.

The problem came, when another person ran the report from a different machine, the same report had a different layout, in the sense that the columns did not line up with the one that was originally pasted in, because this was done by a different user, on a different machine. The other countries had the same problem, regardless if of user or machine.


I put a new tab in called New Raw data dump because if I had put it in Download Raw Proposal tab, and accidentally pressed the macro button - it would have wiped everything that was in the tab, so I just did it as a fail safe.

I see what you mean only needing 7 columns but pasting 12. I would have come to the same conclusion eventually, to be honest I only need

Company Code, Document Number, Document Date, Net Amount, Currency, Error Code from the report, (error code output text, is entered later via the macro). the rest can be scrapped.
Everything runs from the Download Raw Proposal, which is basically the messy Raw data from SAP, so besides the 4 tabs - download Raw Proposal + run report + pay proposal + lookups, no other tabs are needed.

the overall objective is is to get every report of varying layouts, into one standard layout, and then the macro will sort it out and make it look nice in tab 3.

from there - users investigate the issues and resolve them - which is why it needs to look like tab 3 as its more workable then a raw SAP report, which is spread out across a worksheet.
so ideally you could dump any SAP Proposal report into download Raw proposal tab from any layout and the macro would organise it in tab 3.


The variants in SAP are supposed to be universal, if one person creates it, all should have the same layout, in the same columns etc, but it doesnt work like that, and its not down to versions of SAP, as Ive stubble across this problem before in other companies, but this is the first time I am tackling it.


Hope that makes sense, and I really appreciate your help.

thanks

David.

 

[mumps]

Try:

Sub PreparePayReport()
    Application.ScreenUpdating = False
    Dim LastRow As Long, LastRow2 As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("1) Download RAW Proposal")
    Set desWS = Sheets("3) Pay Proposal Report")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Intersect(srcWS.Rows("8:" & LastRow), Range("C:C,D:D,F:F,I:I,N:N,Y:Y,Z:Z,AB:AB")).Copy desWS.Cells(4, 1)
    LastRow2 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With desWS.Range("I4")
        .Formula = "=IF(ISBLANK(H4),"""",VLOOKUP(H4,Lookups!A:B,2,FALSE))"
        .AutoFill .Resize(LastRow2 - 4, 1)
    End With
    With srcWS.UsedRange
        .Copy Sheets("New Raw data dump").Cells(1, 1)
        .ClearContents
    End With
    Application.ScreenUpdating = True
End Sub

 

[Dave01]

Hi,

I get an error -

Run-time Error 1004, Method Intersect of object_Global failed

(I have no idea what than means)

which is this line of the code

Intersect(srcWS.Rows("8:" & LastRow), Range("C:C,D:D,F:F,I:I,N:N,Y:Y,Z:Z,AB:AB")).Copy desWS.Cells(4, 1)


so what I did was get the FR07 file, and copy and paste it into 1)Download Raw Proposal Tab. I made a new macro - Made it blank, and pasted in your code, the ran it.

Thanks

David.

 

[mumps]

You are very welcome. The macro assumes that the data is in the "Download Raw Proposal" sheet to begin with and it makes a copy of it in the "New Raw data dump" sheet.