Monthly excel VBA report

T

taeas

New Member
Joined
May 2, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi

Apologies if this is an so appearantly easy question that preschoolers can do it in their sleet :) i have looked but i cant find a good solution.
But the only way i can see is VBA.
Because i cant touch the "Data" sheet, due it will return a bad format in the production system causing the report to crash.
My goal is to return monthly data values from the "data" sheet to the predefined template set by our customer in the "Monthly Report" tab Where the headers have comment on what data field in "data" that corresponds to the field header.

So the case is: all formulas needs to be defined in the "Monthly Report" sheet, or in VBA.
"Data" Sheet
Copy of Monthly Wellboat log test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Unit NameForm No.Custom Multiline Field 1Form Template No.Form Template NameCreate DateStatusCountCustom Date Field 1Custom Date Field 2Custom Date Field 3Custom Date Field 4Custom Date Field 5Custom Numeric Field 1Custom Numeric Field 10Custom Numeric Field 2Custom Numeric Field 3Custom Numeric Field 4Custom Numeric Field 5Custom Numeric Field 6Custom Numeric Field 7Custom Numeric Field 8Custom Numeric Field 9Custom Text Field 1Custom Text Field 10Custom Text Field 2Custom Text Field 3Custom Text Field 4Custom Text Field 5Custom Text Field 6Custom Text Field 7Custom Text Field 8Custom Text Field 9
2Aqua TEST19828792-2020-APPCOM007-0512020-135APPCOM007Shipping Letter WB11/1/20Approved110/30/2010/30/2010/30/2010/30/2011/1/20120.150.1515459415545623.623.8310050MOWITransfer SmoltN/AYesYtre standal - bastlid64Closed
3Aqua TEST29828792-2021-APPCOM007-0232021/ 0012APPCOM007Shipping Letter WB1/24/21Approved11/23/211/23/211/23/211/23/211/24/217.50.120.15169612170525340137MOWITransfer SmoltN/AYesSteinsvik til VoldnesM9M10Closed
Data


Monthly Report :
Vessel field=Unit Name
Charter=Customfield1
Site/Location=CustomFieldText5
Work Description=CustomFieldText2
Fish Loaded=SUM of CustomFieldNumeric4&5


Copy of Monthly Wellboat log test.xlsx
BCDEFGHIJKLMNOPQRSTUVWX
6Month :
7Vessel:
8
9Date Charter Shipping Letter No.:Production AreaSite / Location Harbour Work description Average Weight Fish Loaded Biomass Treated Dead on arrivalOil / Sludge disposal Refuelling Chemical UsedSailing distanceSailing timeTime consumed Comments
10[YYYY-NNN] (P04 etc.)[kg][pcs][ton][pcs][l][l][l][nm][h]Work [h]Harbour [h]Wash [h]Ozonation [h]Veterinarian control Quarantine
11
121/1/210.0
131/2/21
141/3/21
151/4/21
Monthly Report
Cell Formulas
RangeFormula
K12K12=J12*I12/1000


Any help performing this task will be appreciated !
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
The questions are:
1) Data sheet will contain data for each month or data for whole year?
2) Do you need to extract data from Data sheet based on Vessel name?
 
Upvote 0
Zot said:
The questions are:
1) Data sheet will contain data for each month or data for whole year?
2) Do you need to extract data from Data sheet based on Vessel name?
Click to expand...
Hi Zot.
Thanks for replying.

1)Month, but the report template im working on compiles data from 01.01./start of year.
2)Yes, by vessel name/unitname. I can also amend the template from the production software to only generate one unit at a time. But report template in question has multiple units included.
 
Upvote 0
taeas said:
Hi Zot.
Thanks for replying.

1)Month, but the report template im working on compiles data from 01.01./start of year.
2)Yes, by vessel name/unitname. I can also amend the template from the production software to only generate one unit at a time. But report template in question has multiple units included.
Click to expand...
How do you plan to input vessel name? I was thinking Data Validation, but the list is on sheet Data that might change. Not possible unless you have fixed list but not too long or else manually type in.
 
Upvote 0
Zot said:
How do you plan to input vessel name? I was thinking Data Validation, but the list is on sheet Data that might change. Not possible unless you have fixed list but not too long or else manually type in.
Click to expand...
Vessel name is fixed metadata, that will populate automatically from the production software.
UNITID:
ABOR
AHAV
AHOM
AKVA
AMAL
ASAN
ASKI
ASKY
ASPA
ATRO

If the unit generates the report template, it will only get its own data, meaning only the UNITID or UNITNAME that corrosponds to the ID.
Or can it be a dropdown basis above, appreciate your inputs?
 
Upvote 0
Zot said:
How do you plan to input vessel name? I was thinking Data Validation, but the list is on sheet Data that might change. Not possible unless you have fixed list but not too long or else manually type in.
Click to expand...
I have another question, can i use, or is it a good idea to use index/match function on the Monthly Report sheet, that pulls data from "Data" sheet, based on unit ID, and month?
 
Upvote 0
taeas said:
I have another question, can i use, or is it a good idea to use index/match function on the Monthly Report sheet, that pulls data from "Data" sheet, based on unit ID, and month?
Click to expand...
I'm sure it can be done.
You data sheet for each month. You Report is for a year duration. Your Report formula will need to link to all 12 data workbooks. Need to be in same network I guess.
If your report is monthly then less messy.

For me, I don't like to have files full with formula especially long formula. Makes file big. :giggle:

I was thinking of creating report on monthly basis for each vessel. Since each month I imagined that you will not have same vessel or add new vessel (uncertain vessel list) and also to keep the list as short as possible, I was thinking of Validation list for convenience and prevent typo error.

My thinking is having Monthly Report template with 2 buttons to get vessel list and another button to compile. Since the vessel list would be changing all the time, after compilation I would just remove the Validation List and jut put the vessel name. The Validation List is no longer usable since the report generated is specifically for that vessel. Open template again and run compilation for another vessel.
 
Upvote 0
Zot said:
I'm sure it can be done.
You data sheet for each month. You Report is for a year duration. Your Report formula will need to link to all 12 data workbooks. Need to be in same network I guess.
If your report is monthly then less messy.

For me, I don't like to have files full with formula especially long formula. Makes file big. :giggle:

I was thinking of creating report on monthly basis for each vessel. Since each month I imagined that you will not have same vessel or add new vessel (uncertain vessel list) and also to keep the list as short as possible, I was thinking of Validation list for convenience and prevent typo error.

My thinking is having Monthly Report template with 2 buttons to get vessel list and another button to compile. Since the vessel list would be changing all the time, after compilation I would just remove the Validation List and jut put the vessel name. The Validation List is no longer usable since the report generated is specifically for that vessel. Open template again and run compilation for another vessel.
Click to expand...
@Zot

The above sounds good and logical to me, but to be honest, i spent 6 hours yesterday in VBA modules, without any good result. So i need help if you are able to give it :)
The report template has a dynamic date filter in the create date, so the data in "Data" will only be 31 days backwards, and not a whole year. when the template is run.
There is also a Report Filter "sheet in the template, where data validation can be done, here is all the criterias set? will this help?

Copy of Monthly Wellboat log.xlsx
ABCDEFGHIJKL
1Selected Template:List of Forms
2
3Included columns:Filter type:Criteria from:Criteria to:Summed:Top criteria:Field compare:Order:Sort type:List Filter:
4Unit NameFrom List – Select data from listABOR AHAV AHOM AKVA AMAL ASAN ASKI ASKY ASPA ATRO0Include
5Form No.No filter
6Custom Multiline Field 1No filter
7Form Template No.From List – Select data from listAPPCOM0070Include
8Form Template NameNo filter0
9Create DateDynamic time range - (e.g. of one week ahead, whole quarter etc.)>= 01.05.2021<= 31.05.20210
10StatusNo filter
11CountNo filter
12Custom Date Field 1No filter
13Custom Date Field 2No filter
14Custom Date Field 3No filter
15Custom Date Field 4No filter
16Custom Date Field 5No filter
17Custom Numeric Field 1No filter
18Custom Numeric Field 10No filter
19Custom Numeric Field 2No filter
20Custom Numeric Field 3No filter
21Custom Numeric Field 4No filter
22Custom Numeric Field 5No filter
23Custom Numeric Field 6No filter
24Custom Numeric Field 7No filter
25Custom Numeric Field 8No filter
26Custom Numeric Field 9No filter
27Custom Text Field 1No filter
28Custom Text Field 10No filter
29Custom Text Field 2No filter
30Custom Text Field 3No filter
31Custom Text Field 4No filter
32Custom Text Field 5No filter
33Custom Text Field 6No filter
34Custom Text Field 7No filter
35Custom Text Field 8No filter
36Custom Text Field 9No filter
37Custom Numeric Field 20No filter
38Custom Numeric Field 14No filter
39Custom Multiline Field 11No filter
40Custom Numeric Field 12No filter
41Custom Numeric Field 11No filter
Report Filter
 
Upvote 0
I have not tried the code extensively. The parameter you wanted to transfer just few sample and I have not verified if they are transferred right since not all variables you said actually match with your Data sheet.

When you execute GetList, it will create a Vessel sheet for my temporary work such as creating unique list of vessels for Validation List. It can be deleted after you execute 2nd code CompileReport which I did not do in my code yet.

Since you are also have experience in VBA, I believe you can easily modify the code to meet your need. I was not able to iron out the code since too busy with work today and also yesterday.
VBA Code: 
Sub GetList()

Dim n As Long, rowReport As Long
Dim colUName As String
Dim FName As Variant
Dim rngUName As Range, rowLast As Range
Dim wsFound As Boolean
Dim ws As Worksheet, wsData As Worksheet, wsReport As Worksheet, wsVessel As Worksheet
Dim wbData As Workbook, wbReport As Workbook

Set wbReport = ActiveWorkbook
Set wsReport = wbReport.Sheets("Sheet1")      ' Change accordingly

' Select Data file
FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If FName = False Then                          'CANCEL is clicked
    Exit Sub
End If

Application.ScreenUpdating = False

Set wbData = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set wsData = wbData.Sheets("Sheet1")              ' Change accordingly

Set rowLast = wsData.Range("A" & Rows.Count).End(xlUp)
Set rngUName = wsData.Range("A1", rowLast)

' Copy Vessel Name from wsData into a sheet in wsReport Sheet named Vessel
For Each ws In wbReport.Sheets
    If ws.Name = "Vessel" Then wsFound = True
Next
If Not wsFound Then
    wbReport.Sheets.Add(After:=wsReport).Name = "Vessel"
End If
Set wsVessel = wbReport.Sheets("Vessel")
wsVessel.Range("A1", "A100").ClearContents
rngUName.Copy wsVessel.Range("A1")
wsVessel.Range("B2", "B20").Formula = "=IFERROR(INDEX(A2:A100,MATCH(0,INDEX(COUNTIF($B$1:B1,A2:A100),),0)),"""")"
wsReport.Range("D6") = Format(wsReport.Range("B12"), "mmm-yyyy")
wsReport.Range("D7").Validation.Delete
wsReport.Range("D7").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=OFFSET(Vessel!$B$2,,,COUNTIF(Vessel!$B$2:$B$20,""?*""))"
wsVessel.Range("D2") = FName

End Sub

Sub CompileReport()

Dim n As Long, rowReport As Long
Dim colUName As String, colCF1 As String, colCTF2 As String, colCTF5 As String
Dim colCNF4 As String, colCNF5 As String, Vessel As String
Dim cell As Range, rngUName As Range, rowLast As Range, rngColData As Range, colLast As Range
Dim wsFound As Boolean
Dim ws As Worksheet, wsData As Worksheet, wsReport As Worksheet, wsVessel As Worksheet
Dim wbData As Workbook, wbReport As Workbook

Set wbReport = ActiveWorkbook
Set wsReport = wbReport.Sheets("Sheet1")      ' Change accordingly
Set wsVessel = wbReport.Sheets("Vessel")

Application.ScreenUpdating = False

Set wbData = Workbooks.Open(Filename:=wsVessel.Range("D2"), UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set wsData = wbData.Sheets("Sheet1")              ' Change accordingly
Set colLast = wsData.Range("A1").End(xlToRight)
Set rngColData = wsData.Range("A1", colLast)
Set rowLast = wsData.Range("A" & Rows.Count).End(xlUp)
Set rngUName = wsData.Range("A1", rowLast)

Vessel = wsReport.Range("D7")
If Vessel = "" Then
    MsgBox "Please select vessel", vbCritical, "SELECT VESSEL"
    End
End If
wsReport.Range("D7").Validation.Delete
wsReport.Range("D7") = Vessel

' Find respective columns
For Each cell In rngColData
    Select Case cell
        Case "Unit Name"
            colUName = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Multiline Field 1"
            colCF1 = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Text Field 2"
            colCTF2 = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Text Field 5"
            colCTF5 = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Numeric Field 4"
            colCNF4 = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Numeric Field 5"
            colCNF5 = Split(cell.Address, "$")(1)
            n = n + 1
    End Select
    If n = 6 Then Exit For
Next

' Get last row containing report in wsReport
rowReport = wsReport.Range("B" & Rows.Count).End(xlUp).Row

'wsreport.Range("D7")=
For Each cell In rngUName
    If cell = wsReport.Range("D7") Then
        rowReport = rowReport + 1
        wsReport.Range("C" & rowReport) = wsData.Range(colCF1 & cell.Row)
        wsReport.Range("F" & rowReport) = wsData.Range(colCTF5 & cell.Row)
        wsReport.Range("H" & rowReport) = wsData.Range(colCTF2 & cell.Row)
        wsReport.Range("J" & rowReport).Formula = "=" & wsData.Range(colCNF4 & cell.Row).Address(0, 0) & _
                                                                                    "+" & wsData.Range(colCNF5 & cell.Row).Address(0, 0)
    End If
Next

End Sub
 
Upvote 0
Zot said:
I have not tried the code extensively. The parameter you wanted to transfer just few sample and I have not verified if they are transferred right since not all variables you said actually match with your Data sheet.

When you execute GetList, it will create a Vessel sheet for my temporary work such as creating unique list of vessels for Validation List. It can be deleted after you execute 2nd code CompileReport which I did not do in my code yet.

Since you are also have experience in VBA, I believe you can easily modify the code to meet your need. I was not able to iron out the code since too busy with work today and also yesterday.
VBA Code: 
Sub GetList()

Dim n As Long, rowReport As Long
Dim colUName As String
Dim FName As Variant
Dim rngUName As Range, rowLast As Range
Dim wsFound As Boolean
Dim ws As Worksheet, wsData As Worksheet, wsReport As Worksheet, wsVessel As Worksheet
Dim wbData As Workbook, wbReport As Workbook

Set wbReport = ActiveWorkbook
Set wsReport = wbReport.Sheets("Sheet1")      ' Change accordingly

' Select Data file
FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb; *.xml), *.xls; *.xlsx; *.xlsm; *.xlsb; *.xml", _
                                                            Title:="Select a File")
If FName = False Then                          'CANCEL is clicked
    Exit Sub
End If

Application.ScreenUpdating = False

Set wbData = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set wsData = wbData.Sheets("Sheet1")              ' Change accordingly

Set rowLast = wsData.Range("A" & Rows.Count).End(xlUp)
Set rngUName = wsData.Range("A1", rowLast)

' Copy Vessel Name from wsData into a sheet in wsReport Sheet named Vessel
For Each ws In wbReport.Sheets
    If ws.Name = "Vessel" Then wsFound = True
Next
If Not wsFound Then
    wbReport.Sheets.Add(After:=wsReport).Name = "Vessel"
End If
Set wsVessel = wbReport.Sheets("Vessel")
wsVessel.Range("A1", "A100").ClearContents
rngUName.Copy wsVessel.Range("A1")
wsVessel.Range("B2", "B20").Formula = "=IFERROR(INDEX(A2:A100,MATCH(0,INDEX(COUNTIF($B$1:B1,A2:A100),),0)),"""")"
wsReport.Range("D6") = Format(wsReport.Range("B12"), "mmm-yyyy")
wsReport.Range("D7").Validation.Delete
wsReport.Range("D7").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=OFFSET(Vessel!$B$2,,,COUNTIF(Vessel!$B$2:$B$20,""?*""))"
wsVessel.Range("D2") = FName

End Sub

Sub CompileReport()

Dim n As Long, rowReport As Long
Dim colUName As String, colCF1 As String, colCTF2 As String, colCTF5 As String
Dim colCNF4 As String, colCNF5 As String, Vessel As String
Dim cell As Range, rngUName As Range, rowLast As Range, rngColData As Range, colLast As Range
Dim wsFound As Boolean
Dim ws As Worksheet, wsData As Worksheet, wsReport As Worksheet, wsVessel As Worksheet
Dim wbData As Workbook, wbReport As Workbook

Set wbReport = ActiveWorkbook
Set wsReport = wbReport.Sheets("Sheet1")      ' Change accordingly
Set wsVessel = wbReport.Sheets("Vessel")

Application.ScreenUpdating = False

Set wbData = Workbooks.Open(Filename:=wsVessel.Range("D2"), UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set wsData = wbData.Sheets("Sheet1")              ' Change accordingly
Set colLast = wsData.Range("A1").End(xlToRight)
Set rngColData = wsData.Range("A1", colLast)
Set rowLast = wsData.Range("A" & Rows.Count).End(xlUp)
Set rngUName = wsData.Range("A1", rowLast)

Vessel = wsReport.Range("D7")
If Vessel = "" Then
    MsgBox "Please select vessel", vbCritical, "SELECT VESSEL"
    End
End If
wsReport.Range("D7").Validation.Delete
wsReport.Range("D7") = Vessel

' Find respective columns
For Each cell In rngColData
    Select Case cell
        Case "Unit Name"
            colUName = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Multiline Field 1"
            colCF1 = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Text Field 2"
            colCTF2 = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Text Field 5"
            colCTF5 = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Numeric Field 4"
            colCNF4 = Split(cell.Address, "$")(1)
            n = n + 1
        Case "Custom Numeric Field 5"
            colCNF5 = Split(cell.Address, "$")(1)
            n = n + 1
    End Select
    If n = 6 Then Exit For
Next

' Get last row containing report in wsReport
rowReport = wsReport.Range("B" & Rows.Count).End(xlUp).Row

'wsreport.Range("D7")=
For Each cell In rngUName
    If cell = wsReport.Range("D7") Then
        rowReport = rowReport + 1
        wsReport.Range("C" & rowReport) = wsData.Range(colCF1 & cell.Row)
        wsReport.Range("F" & rowReport) = wsData.Range(colCTF5 & cell.Row)
        wsReport.Range("H" & rowReport) = wsData.Range(colCTF2 & cell.Row)
        wsReport.Range("J" & rowReport).Formula = "=" & wsData.Range(colCNF4 & cell.Row).Address(0, 0) & _
                                                                                    "+" & wsData.Range(colCNF5 & cell.Row).Address(0, 0)
    End If
Next

End Sub
Click to expand...
Thanks, will try this today. Reverting.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Using VBA to loop through multiple Word Templates and fill them in with Excel Data
2
Replies
14
Views
2K
John_w
J
J
  • Question Question
Cannot close temporary file and delete in VBA for Excel coding
Replies
2
Views
732
jolor69
J
S
  • Question Question
Excel Customer List to Word Offer and Invoice template, with unique invoice number list and buttons to create
Replies
0
Views
517
_SGX_
S
O
  • Question Question
Excel/VBA to conditionally send emails per schedule
Replies
0
Views
953
OC2PS
O
willow1985
  • Solved
VBA Compile error - Trying to send an email using VBA/Excel
Replies
6
Views
1K
gorgkhan
G

Forum statistics

Threads
1,223,978
Messages
6,175,755
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top