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]

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.

oH DAM, I deleted that because I thought it was redundant, let me try that again.

 

[Dave01]

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.

Hi, so I tried it again, but cant get past the error, it wont copy to the new data dump,

 

[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), srcWS.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]

https://www.dropbox.com/sh/ra3ux1n6xdm6ziv/AAD6xBC7AGYfFl9YcChBvv6oa?dl=0

Ive uploaded a fresh set of data raw files and the template. Its much better as the error is gone, but it does not appear to be putting the data in the same columns each time. so not everything is being picked up in the Pay Proposal Report tab.
to be honest the New Raw Data dump, isnt really needed as it can all be done through the yellow tab - download Raw Proposal. - Really I only need 3. Download Raw Proposal, a tab for the macro & the pay proposal report.

I am really grateful for your help, as otherwise, it would be back to the drawing board.

 

[mumps]

The problem was due to the fact that the columns you want to copy in the files you attached were not the same as in your original file. Try:

Sub PrepareSAPPayReport()
    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), srcWS.Range("C:C,E:E,J:J,M:M,S:S,AO:AO,AR:AR,AU:AU")).Copy desWS.Cells(4, 1)
    LastRow2 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With desWS.Range("I4")
        .Formula = "=IFERROR(IF(ISBLANK(H4),"""",VLOOKUP(H4,Lookups!A:B,2,FALSE)),"""")"
        .AutoFill .Resize(LastRow2 - 4, 1)
    End With
    Application.ScreenUpdating = True
End Sub

Does the position of the columns you want to copy change?

 

[Dave01]

The problem was due to the fact that the columns you want to copy in the files you attached were not the same as in your original file. Try:

Sub PrepareSAPPayReport()
    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), srcWS.Range("C:C,E:E,J:J,M:M,S:S,AO:AO,AR:AR,AU:AU")).Copy desWS.Cells(4, 1)
    LastRow2 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With desWS.Range("I4")
        .Formula = "=IFERROR(IF(ISBLANK(H4),"""",VLOOKUP(H4,Lookups!A:B,2,FALSE)),"""")"
        .AutoFill .Resize(LastRow2 - 4, 1)
    End With
    Application.ScreenUpdating = True
End Sub

Does the position of the columns you want to copy change?

NL02 - works great - the bottom half seems to go funny but thats because of the source file, which im going to refer back to the SAP IT Team.


BE02 works great - but this was the original template I used in the first place.


DK01 - works great, again the bottom of he source file goes funny, but thats down to the source file, so im not bothered.


FR07 - almost worked well - but didnt picked up anything after document number.




That was from 1 user and her machine,




altogether - a major breakthough.




I then tried it with another FR07, from a different user fr4om a different machine, the type ended up under document number
where as on the new new test data file evrything was in the correct order up to Document number, then nothing appeared.




so even when we have fixed the issues on 1 users file, theres the issue of it being downloaded on another machine from a different user.




if only there was a way to say -


scan the entire workbook of downloaded Raw proposal, if you find a cell with payment written in it, copy the entire column, and past it in D on the New data dump tab


scan the entire workbook of downloaded Raw proposal, if you find a cell with document no written in it, copy the entire column, and past it in I on the New data dump tab and so on


basically its a vlookup - but it cant copy and paste entire columns, hense it seems VB is the only option.


the only line I understand from the entire code is Formula = "=IFERROR(IF(ISBLANK(H4),"""",VLOOKUP(H4,Lookups!A:B,2,FALSE)),"""")


so I have very little chance.

 

[Dave01]

saying that, find this text past it in that column, it doesnt matter which column it pasts it into, as long as the all the report relevant columns ends up pasted into the same column.

its just a case of a scattered raw report end up in the same column for each.

 

[mumps]

Will these headers in the "1) Download RAW Proposal" always be exactly the same and in the same row (row 6) in every file?
BusA, CoCd, DocumentNo, Doc. Date, Net amnt. in FC, Crcy and Err
Will the Vendor number always be in column C?

 

[Dave01]

Will these headers in the "1) Download RAW Proposal" always be exactly the same and in the same row (row 6) in every file?
BusA, CoCd, DocumentNo, Doc. Date, Net amnt. in FC, Crcy and Err
Will the Vendor number always be in column C?

Hi, thanks for not giving up.

yes the headers BusA, CoCd, DocumentNo, Doc. Date, Net amnt. in FC, Crcy and Err will always be the same in name although be in different columns depending on user and machine, (by the looks of it) which is the fustrating bit.

Vendor number always be in column C - Looking at the test data files (Raw files to be uploaded) using the new data from one person machine, and the original file, it does look like they always fall in column C - however I cant be 100% sure that this will be the case, But i'm more on the sure side that it will be, so I would assume this would be the case.

To get round this, I could always write in the instructions notes, the the raw data data, please make sure vendor is in column C, either by copy and paste, or inserting or deleting columns, im not sure which.

The good thing with this code is, its a real pain with SAP, and ive been in a few companies where this has been a constant issue, now Ive decided to tackle it. SAP is build from the ground up to specifications of that company, yet the payments download screen always seems to have the same issue, they dont seem to like allowing users to custom build their layouts, which is crazy as every other screen - Sales Ledger / Purchase Ledger even the general ledger screen are all customisable.

I suppose if you do manage to crack this, this code, would be a real asset and would need a passport, because its going to end up travailing - I can see that.

again thank you so much for your help.

 

[mumps]

OK. I know SAP can be a pain. My wife deals with it every day. I will proceed to make some changes to the macro. However, the macro will assume that the Vendor number will always be in column C and the headings will always be in row 6. If it still causes problems because of the column and row, we can look at alternative solutions. I'll get back to you as soon as I can.