I´m doing a data management process for which I created a VBA Macros, but recently I discovered that some of the users of the Macros will be using Open Office, I want to know what it takes to make the change from VBA to BA, to see if I should invest the time in doing it or if I should look for a different approach.
The Macro works as follows:
The user will download a data report that has multiple variable number of sheets for each user fill with varaible number of rows as well.
The user will copy all the sheet to the end of the document with the Macro.
The user cliks a button and the macro starts working.
The macro goes into each sheet and copy the data into a "ConsoidatedData" sheet where it consolidates all the data.
Then the Macro updates some pivot tables, then copy the info into formated tables.
Then the formated tables are copied into a new document and the Macro document is closed and the original data sheets erased from it (This is so the user will have the least interaction with the document as possible so it wont alter it, since most of the users will have from none to low technical habilities.
Here is the code:
Code: Select all
Sub dataUpdate()
Application.ScreenUpdating = False
'Erase previous data'
Worksheets("ConsolidatedData").Range("A2:V10000").ClearContents
'Loop that goes through each data sheet copy its content and paste it into the "ConsolidatedData" Sheet '
Dim ws As Worksheet
Dim data As Worksheet
Set data = Worksheets("ConsolidatedData")
For x = 6 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(x)
'Clean Up of the data sheets so the copying process can be standarize of each one'
ws.Range("A1:A7").UnMerge
If InStr(1, ws.Range("A1").End(xlDown), "50 NET STORE PROFIT") = False Then
ws.Range("A1").End(xlDown).UnMerge
ws.Range("A1").End(xlDown).ClearContents
End If
'Copy/Paste of data from data sheets to consolidateddata sheet'
ws.Range(ws.Range("U9"), ws.Range("U9").End(xlToLeft).End(xlDown)).Copy _
Destination:=data.Range("B1").End(xlDown).Offset(1)
Application.CutCopyMode = False
Next x
'Updating pivots of the "pivots" sheet and copying data into formated tables in the "tables" sheets'
ThisWorkbook.RefreshAll
Dim pivots As Worksheet
Dim tablas As Worksheet
Set tables = Worksheets("Tablas")
Set pivots = Worksheets("Pivots")
'Updating title for each table(the title changes according to the moth of the data)'
pivots.Range("P4:AC4").Copy
tables.Range("C3").PasteSpecial Paste:=xlPasteValues
pivots.Range("P20:AC20").Copy
tables.Range("C20").PasteSpecial Paste:=xlPasteValues
pivots.Range("P35:AD35").Copy
tables.Range("C36").PasteSpecial Paste:=xlPasteValues
pivots.Range("P50:AC50").Copy
tables.Range("C52").PasteSpecial Paste:=xlPasteValues
'Copying/pasting the data'
pivots.Range(pivots.Range("A5"), pivots.Range("A5").End(xlToRight).Offset(0, -1).End(xlDown).Offset(-1)).Copy
tables.Range("D6").PasteSpecial Paste:=xlPasteValues
pivots.Range(pivots.Range("A21"), pivots.Range("A21").End(xlToRight).Offset(0, -1).End(xlDown).Offset(-1)).Copy
tables.Range("D23").PasteSpecial Paste:=xlPasteValues
pivots.Range(pivots.Range("A36"), pivots.Range("A36").End(xlToRight).Offset(0, -1).End(xlDown).Offset(-1)).Copy
tables.Range("D39").PasteSpecial Paste:=xlPasteValues
pivots.Range(pivots.Range("A51"), pivots.Range("A51").End(xlToRight).Offset(0, -1).End(xlDown).Offset(-1)).Copy
tables.Range("D55").PasteSpecial Paste:=xlPasteValues
'Updating Month'
tables.Range("A24").Copy
tables.Range("C38").PasteSpecial Paste:=xlPasteValues
'Erase Data Sheets'
Dim y As Integer
ThisWorkbook.Worksheets(4).Select
For y = 5 To ThisWorkbook.Worksheets.Count
Worksheets(y).Select (False)
Next y
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
'Copy formated tables into new document'
'Dim Variables'
Dim wb_inp As Workbook
Dim ws_inp As Worksheet
Dim wb_out As Workbook
Dim ws_out As Worksheet
'Set Variables'
Set wb_inp = ThisWorkbook
Set ws_inp = wb_inp.Sheets("Tablas")
Set wb_out = Workbooks.Add
With wb_out
Set ws_out = wb_out.Worksheets(1)
ws_inp.Range("C3:Q100").Copy
ws_out.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
wb_out.Activate
wb_inp.Close
MsgBox "Data Updated"
Application.ScreenUpdating = True
End Sub
Are the functionalities easely transferable like updating pivots, and Ranges for copy pasting? The ranges and end´s are an important part since the size of the data is dynamic, is there something like this in BA?
Should I give a try to change into BA or is it better to look for a different approach?
Thanks in advance for your time and help!