Hi,
I have already created my macro in excel vba and works completely fine; however, I am unable to create it in Libreoffice on given version.
Here are the macros which works fine on ms excel vba:
1st one is to add details:
Sub Add()
Dim wb As Workbook
If Sheet3.Range("D6").Value = "" Then
MsgBox "Please add Associate ID into Database"
Exit Sub
Else
If Sheet3.Range("D8").Value = "" Then
MsgBox "Please add Associate Name into Database"
Exit Sub
Else
ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Copy
Set wb = Workbooks.Open(Range("H1") & "Database.xlsm")
wb.Worksheets("Employee Details").Activate
lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow1 + 1, 1).Select
ActiveCell.PasteSpecial xlPasteValues
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
Selection.Copy
wb.Worksheets("Employee Details").Activate
lastrow2 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Cells(lastrow2 + 1, 2).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Set wb = Nothing
Application.CutCopyMode = False
ThisWorkbook.Activate
MsgBox "Associate Name - " & Cells(8, 4).Value & " & Associate ID - " & Cells(6, 4) & " Added Successfully"
ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
Selection.ClearContents
ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
Selection.ClearContents
End If
End If
End Sub
2nd one is for allocation almost similar to add
Sub Allocate()
Dim wb As Workbook
Dim Key As Variant
If Range("G10") = " " Then
MsgBox "Please add associate into Database"
Exit Sub
Else
If Range("D8") = "" Then
MsgBox "Please Input Key No."
Else
Key = Range("D8")
Set wb = Workbooks.Open(ThisWorkbook.Worksheets("Employee Addition").Range("H1") & "Database.xlsm")
wb.Worksheets("Transactions").Activate
Range("B1").Select
Set Rng = Cells.Find(What:=Key, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Rng Is Nothing Then
'Copy & Paste Key No. To Another Workbook
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(8, 4).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
lastrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Cells(lastrow1 + 1, 2).Select
ActiveCell.PasteSpecial xlPasteValues
'Copy & Paste Associate ID To Another Workbook
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(10, 4).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
lastrow2 = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
ActiveSheet.Cells(lastrow2 + 1, 3).Select
ActiveCell.PasteSpecial xlPasteValues
'Copy & Paste Associate Name To Another Workbook
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(10, 7).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
lastrow3 = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
ActiveSheet.Cells(lastrow3 + 1, 4).Select
ActiveCell.PasteSpecial xlPasteValues
'Copy & Paste Allocation To Another Workbook
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(12, 4).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
lastrow3 = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
ActiveSheet.Cells(lastrow3 + 1, 5).Select
ActiveCell.PasteSpecial xlPasteValues
Columns("B:J").Sort Key1:=Range("F2"), Order1:=xlDescending, Key2:=Range("G2") _
, Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal
ActiveWorkbook.Save
ActiveWorkbook.Close
Else
If Rng = Key And Cells(Rng.Row, 5) = "Yes" And Cells(Rng.Row, 8) = "Yes" Then
'Copy & Paste Key No. To Another Workbook
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(8, 4).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
lastrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Cells(lastrow1 + 1, 2).Select
ActiveCell.PasteSpecial xlPasteValues
'Copy & Paste Associate ID To Another Workbook
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(10, 4).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
lastrow2 = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
ActiveSheet.Cells(lastrow2 + 1, 3).Select
ActiveCell.PasteSpecial xlPasteValues
'Copy & Paste Associate Name To Another Workbook
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(10, 7).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
lastrow3 = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
ActiveSheet.Cells(lastrow3 + 1, 4).Select
ActiveCell.PasteSpecial xlPasteValues
'Copy & Paste Allocation To Another Workbook
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(12, 4).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
lastrow3 = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
ActiveSheet.Cells(lastrow3 + 1, 5).Select
ActiveCell.PasteSpecial xlPasteValues
Columns("B:J").Sort Key1:=Range("F2"), Order1:=xlDescending, Key2:=Range("G2") _
, Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal
ActiveWorkbook.Save
ActiveWorkbook.Close
Else
If Rng = Key And Cells(Rng.Row, 5) = "Yes" And Cells(Rng.Row, 8) = "" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
MsgBox "Key Already Allocated"
End If
End If
End If
End If
End If
MsgBox "Key - " & Cells(8, 4) & " Allocated To - " & Cells(10, 7).Value & " - " & Cells(10, 4)
Range("D8").ClearContents
Range("D10").ClearContents
End Sub
3rd one is for return
Sub Retrn()
Dim wb As Workbook
Dim Key As Variant
If Range("D8") = "" Then
MsgBox "Please Input Key No."
Exit Sub
Else
Key = Range("D8")
Set wb = Workbooks.Open(ThisWorkbook.Worksheets("Employee Addition").Range("H1") & "Database.xlsm")
wb.Worksheets("Transactions").Activate
Range("B1").Select
Set Rng = Cells.Find(What:=Key, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Rng Is Nothing Then
MsgBox "Key Is Not Allocated"
Else
If Rng = Key And Cells(Rng.Row, 5) = "Yes" And Cells(Rng.Row, 8) = "Yes" Then
MsgBox "Key Is Not Allocated"
Else
If Rng = Key And Cells(Rng.Row, 5) = "Yes" And Cells(Rng.Row, 8) = "" Then
'Copy & Paste Key No. To Another Workbook
ActiveSheet.Cells(Rng.Row, 3).Select
Selection.Copy
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Allocation").Cells(10, 4).Select
ActiveCell.PasteSpecial xlPasteValues
ThisWorkbook.Worksheets("Allocation").Cells(12, 4).Select
Selection.Copy
wb.Worksheets("Transactions").Activate
ActiveSheet.Cells(Rng.Row, 8).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End If
End If
End If
MsgBox "Key - " & Cells(8, 4) & " Returned By - " & Cells(10, 7).Value & " - " & Cells(10, 4)
Range("D8").ClearContents
Range("D10").ClearContents
End Sub
Need help to convert all these to Libre Office Macros at the given Version: 6.3.5.2.