Long report from Navsion - needs macro

[dummeole]

Hi,

I have a report from Navision with about 6000 lines, so I figure I need a macro to help me out, as I have to do this on a monthly basis. I'm not an expert so somehow I get lost in all the looping and logic.

Below I only show a short bit of the report. I would like to end up with what I have in range L1:P3 (Preferably in a new sheet called "output"). Don't worry about the colors, they're only there to show where the data comes from.

A Customer should only end up in the output list if there anything to sum under "KG". This is why only "Kunde 1" and "Kunde 4" is in my output.

Can anybody please help?

Thanks
Ole

 

[dummeole]

Sorry, forgot to mention that I'm using Excel 2010 and Win 7.

 

[par60056]

Welcome to the forum.

Took me a few minutes. to reply. There are a couple approaches you can take.

1) You could try to collapse the data into a raw data sheet that you could build a pivot table from. This would mean writing a script to insert the date, country, customer number and customer name on each data line and remove all the extra blank lines.

2) You could read through the file and identify key information and build the result page as you go.

I tend to make everything a pivot table but in this case I think choice 2 is easier.

I wrote up something that uses method 2

Sub report()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim custStartRow As Long
Dim currRow As Long
Dim outRow As Long


Dim theDate As String
Dim theCountry As String
Dim theCustNo As String
Dim theCustomer As String
Dim canOutput As Boolean


Set sourceSheet = ActiveSheet
Set targetSheet = Worksheets.Add


targetSheet.Name = "Output"
'add headers
Cells(1, 1) = "Country"
Cells(1, 2) = "Date"
Cells(1, 3) = "Customer Number"
Cells(1, 4) = "Customer"
Cells(1, 5) = "Sum of KG"
outRow = 2


' go back to the source sheet after adding the output sheet
sourceSheet.Activate
theDate = Right(Cells(3, 3), 8) ' Assumes ending date is 8 characters
theCountry = Mid(Cells(4, 3), InStr(Cells(4, 3), ":") + 1) ' Assumes there is a ':' before the country name


lastRow = sourceSheet.UsedRange.Rows.Count + sourceSheet.UsedRange.Row
For currRow = 8 To lastRow
    If (Cells(currRow, 3) = "" And Cells(currRow, 1) <> "") Then
        theCustNo = Cells(currRow, 1)
        theCustomer = Cells(currRow, 2)
    ElseIf Cells(currRow, 1) = "Varenr." Then
        custStartRow = currRow + 1
        canOutput = True
    ElseIf Cells(currRow, 1) = "" And canOutput Then
        If (custStartRow < currRow) Then
            targetSheet.Cells(outRow, 1) = theCountry
            targetSheet.Cells(outRow, 2) = theDate
            targetSheet.Cells(outRow, 3) = theCustNo
            targetSheet.Cells(outRow, 4) = theCustomer
            targetSheet.Cells(outRow, 5) = "=sum(" & sourceSheet.Name & "!E" & custStartRow & ":E" & currRow - 1 & ")"
            outRow = outRow + 1
        End If
        canOutput = False
    End If
Next


End Sub

 

[dummeole]

Hi,

Thank you so much for your help

When I try to run the macro I get this error.

Run-time error '1004': Application-defined or object-defined error.

It refers to this line:

targetSheet.Cells(outRow, 5) = "=sum(" & sourceSheet.Name & "!E" & custStartRow & ":E" & currRow - 1 & ")"

Can you tell me how to proceed?

 

[par60056]

I suspect that the source sheet has a space in the sheet name. I try not to have spaces in sheet names and I forget other people allow it.

Put a single quote inside the double quotes before and after the source sheet name. like this:

targetSheet.Cells(outRow, 5) = "=sum('" & sourceSheet.Name & "'!E" & custStartRow & ":E" & currRow - 1 & ")"

 

[dummeole]

Yep, that was it. Now it works. Thank you so much