বুধবার, ২৭ জুলাই, ২০১৬
মঙ্গলবার, ১৯ জুলাই, ২০১৬
Tanesfar
Private Sub CommandButton1_Click()
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 4 To LastRow
If Cells(i, 6) = "Today" Then
Range(Cells(i, 1), Cells(i, 5)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\robi\Desktop\Client.xlsx"
Dim p As Integer, q As Integer
p = Worksheets.Count
For q = 1 To p
If ActiveWorkbook.Worksheets(q).Name = "robiul" Then
Worksheets("robi").Select
End If
Next q
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i
End Sub
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 4 To LastRow
If Cells(i, 6) = "Today" Then
Range(Cells(i, 1), Cells(i, 5)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\robi\Desktop\Client.xlsx"
Dim p As Integer, q As Integer
p = Worksheets.Count
For q = 1 To p
If ActiveWorkbook.Worksheets(q).Name = "robiul" Then
Worksheets("robi").Select
End If
Next q
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i
End Sub
শুক্রবার, ১৫ জুলাই, ২০১৬
wksinput
Option Explicit
Private Sub CommandButton1_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Robiul")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton10_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Tamzid")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton11_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Nobel")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton12_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Shajedul")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton13_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Hasan")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton14_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Monir")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton15_Click()
On Error Resume Next
Worksheets("Robiul").Activate
End Sub
Private Sub CommandButton16_Click()
On Error Resume Next
Worksheets("Borhan").Activate
End Sub
Private Sub CommandButton17_Click()
On Error Resume Next
Worksheets("Sobuj").Activate
End Sub
Private Sub CommandButton18_Click()
On Error Resume Next
Worksheets("Uzzal").Activate
End Sub
Private Sub CommandButton19_Click()
On Error Resume Next
Worksheets("Pias").Activate
End Sub
Private Sub CommandButton2_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Borhan")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton20_Click()
On Error Resume Next
Worksheets("Mukter").Activate
End Sub
Private Sub CommandButton21_Click()
On Error Resume Next
Worksheets("Ashraf").Activate
End Sub
Private Sub CommandButton22_Click()
On Error Resume Next
Worksheets("Mainuddin").Activate
End Sub
Private Sub CommandButton23_Click()
On Error Resume Next
Worksheets("Jahid").Activate
End Sub
Private Sub CommandButton24_Click()
On Error Resume Next
Worksheets("Tamzid").Activate
End Sub
Private Sub CommandButton25_Click()
On Error Resume Next
Worksheets("Nobel").Activate
End Sub
Private Sub CommandButton26_Click()
On Error Resume Next
Worksheets("Shajedul").Activate
End Sub
Private Sub CommandButton27_Click()
On Error Resume Next
Worksheets("Hasan").Activate
End Sub
Private Sub CommandButton28_Click()
On Error Resume Next
Worksheets("Monir").Activate
End Sub
Private Sub CommandButton3_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Sobuj")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton4_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Uzzal")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton5_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Pias")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton6_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Mukter")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton7_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Ashraf")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton8_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Mainuddin")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton9_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Jahid")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Set rngA = ActiveCell
If Target.Address = Me.Range("CurrRec").Address _
Or Target.Address = Me.Range("OrderSel").Address Then
Application.EnableEvents = False
If Target.Address = Me.Range("OrderSel").Address Then
Me.Range("CurrRec").Value = Me.Range("SelRec").Value
End If
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec > 0 And lRec <= lLastRec Then
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
rngA.Select
End If
End With
Application.EnableEvents = True
End If
End Sub
Private Sub CommandButton1_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Robiul")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton10_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Tamzid")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton11_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Nobel")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton12_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Shajedul")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton13_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Hasan")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton14_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Monir")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton15_Click()
On Error Resume Next
Worksheets("Robiul").Activate
End Sub
Private Sub CommandButton16_Click()
On Error Resume Next
Worksheets("Borhan").Activate
End Sub
Private Sub CommandButton17_Click()
On Error Resume Next
Worksheets("Sobuj").Activate
End Sub
Private Sub CommandButton18_Click()
On Error Resume Next
Worksheets("Uzzal").Activate
End Sub
Private Sub CommandButton19_Click()
On Error Resume Next
Worksheets("Pias").Activate
End Sub
Private Sub CommandButton2_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Borhan")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton20_Click()
On Error Resume Next
Worksheets("Mukter").Activate
End Sub
Private Sub CommandButton21_Click()
On Error Resume Next
Worksheets("Ashraf").Activate
End Sub
Private Sub CommandButton22_Click()
On Error Resume Next
Worksheets("Mainuddin").Activate
End Sub
Private Sub CommandButton23_Click()
On Error Resume Next
Worksheets("Jahid").Activate
End Sub
Private Sub CommandButton24_Click()
On Error Resume Next
Worksheets("Tamzid").Activate
End Sub
Private Sub CommandButton25_Click()
On Error Resume Next
Worksheets("Nobel").Activate
End Sub
Private Sub CommandButton26_Click()
On Error Resume Next
Worksheets("Shajedul").Activate
End Sub
Private Sub CommandButton27_Click()
On Error Resume Next
Worksheets("Hasan").Activate
End Sub
Private Sub CommandButton28_Click()
On Error Resume Next
Worksheets("Monir").Activate
End Sub
Private Sub CommandButton3_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Sobuj")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton4_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Uzzal")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton5_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Pias")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton6_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Mukter")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton7_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Ashraf")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton8_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Mainuddin")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
Private Sub CommandButton9_Click()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Jahid")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
End Sub
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Set rngA = ActiveCell
If Target.Address = Me.Range("CurrRec").Address _
Or Target.Address = Me.Range("OrderSel").Address Then
Application.EnableEvents = False
If Target.Address = Me.Range("OrderSel").Address Then
Me.Range("CurrRec").Value = Me.Range("SelRec").Value
End If
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec > 0 And lRec <= lLastRec Then
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
rngA.Select
End If
End With
Application.EnableEvents = True
End If
End Sub
moddata
Option Explicit
' Developed by Md Robiul awal.
' Mobile : 01748340718
Sub StartNewRecord()
Dim inputWks As Worksheet
Dim listWks As Worksheet
Dim rngClear As Range
Dim rngNext As Range
Dim rngID As Range
Set inputWks = Worksheets("Input")
Set listWks = Worksheets("LookupLists")
Set rngClear = inputWks.Range("DataEntryClear")
Set rngID = inputWks.Range("IDNum")
Set rngNext = listWks.Range("NextID")
rngClear.ClearContents
rngID.Value = rngNext.Value
inputWks.Activate
rngID.Offset(1, 0).Activate
End Sub
Sub UpdateLogWorksheet()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
If inputWks.Range("CheckID") = True Then
lRsp = MsgBox("IMP - Number already Exit.Please Check?", vbQuestion + vbYesNo, "Duplicate ID")
If lRsp = vbYes Then
UpdateLogRecord
Else
MsgBox "Please change IMP Number to a unique number."
End If
Else
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With myCopy.Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End If
End Sub
Sub UpdateLogRecord()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim lRec As Long
Dim oCol As Long
Dim lRecRow As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
If inputWks.Range("CheckID") = False Then
lRsp = MsgBox("IMP Number not in Master data. Add record?", vbQuestion + vbYesNo, "New Order ID")
If lRsp = vbYes Then
UpdateLogWorksheet
Else
MsgBox "Please select IMP Number that is in the Master Data."
End If
Else
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
lRec = inputWks.Range("CurrRec").Value
lRecRow = lRec + 1
With inputWks
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
With .Cells(lRecRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
.Cells(lRecRow, "K").Value = Application.UserName
oCol = 3
myCopy.Copy
.Cells(lRecRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With myCopy.Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
If .Range("ShowMsg").Value = "Yes" Then
MsgBox "Master data has been updated."
End If
End With
End If
End Sub
Sub DeleteLogRecord()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim lRec As Long
' Dim oCol As Long
Dim lRecRow As Long
Dim lDel As Long
Dim strOrder As String
Dim myCopy As Range
Dim myTest As Range
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
strOrder = inputWks.Range("OrderSel").Value
lRec = inputWks.Range("CurrRec").Value
lRecRow = lRec + 1
'cells to clear after deleting record
Set myCopy = inputWks.Range("OrderEntry")
lDel = MsgBox("Delete IMP- " & strOrder & "?", vbCritical + vbYesNo, "Delete IMP-")
If lDel = vbYes Then
With historyWks
With .Cells(lRecRow, "A")
Application.DisplayAlerts = False
.EntireRow.Delete
Application.DisplayAlerts = True
End With
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With myCopy.Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
Else
MsgBox "Delete Success"
End If
End Sub
' Developed by Md Robiul awal.
' Mobile : 01748340718
Sub StartNewRecord()
Dim inputWks As Worksheet
Dim listWks As Worksheet
Dim rngClear As Range
Dim rngNext As Range
Dim rngID As Range
Set inputWks = Worksheets("Input")
Set listWks = Worksheets("LookupLists")
Set rngClear = inputWks.Range("DataEntryClear")
Set rngID = inputWks.Range("IDNum")
Set rngNext = listWks.Range("NextID")
rngClear.ClearContents
rngID.Value = rngNext.Value
inputWks.Activate
rngID.Offset(1, 0).Activate
End Sub
Sub UpdateLogWorksheet()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
If inputWks.Range("CheckID") = True Then
lRsp = MsgBox("IMP - Number already Exit.Please Check?", vbQuestion + vbYesNo, "Duplicate ID")
If lRsp = vbYes Then
UpdateLogRecord
Else
MsgBox "Please change IMP Number to a unique number."
End If
Else
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
'enter date and time stamp in record
With .Cells(nextRow, "L")
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
'enter user name in column B
.Cells(nextRow, "A").Value = Application.UserName
'enter user name in column B
.Cells(nextRow, "K").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With myCopy.Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End If
End Sub
Sub UpdateLogRecord()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim lRec As Long
Dim oCol As Long
Dim lRecRow As Long
Dim myCopy As Range
Dim myTest As Range
Dim lRsp As Long
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
oCol = 3 'order info is pasted on data sheet, starting in this column
'check for duplicate order ID in database
If inputWks.Range("CheckID") = False Then
lRsp = MsgBox("IMP Number not in Master data. Add record?", vbQuestion + vbYesNo, "New Order ID")
If lRsp = vbYes Then
UpdateLogWorksheet
Else
MsgBox "Please select IMP Number that is in the Master Data."
End If
Else
'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")
lRec = inputWks.Range("CurrRec").Value
lRecRow = lRec + 1
With inputWks
Set myTest = myCopy.Offset(0, 2)
If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
With .Cells(lRecRow, "J")
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
.Cells(lRecRow, "K").Value = Application.UserName
oCol = 3
myCopy.Copy
.Cells(lRecRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With myCopy.Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
If .Range("ShowMsg").Value = "Yes" Then
MsgBox "Master data has been updated."
End If
End With
End If
End Sub
Sub DeleteLogRecord()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim lRec As Long
' Dim oCol As Long
Dim lRecRow As Long
Dim lDel As Long
Dim strOrder As String
Dim myCopy As Range
Dim myTest As Range
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
strOrder = inputWks.Range("OrderSel").Value
lRec = inputWks.Range("CurrRec").Value
lRecRow = lRec + 1
'cells to clear after deleting record
Set myCopy = inputWks.Range("OrderEntry")
lDel = MsgBox("Delete IMP- " & strOrder & "?", vbCritical + vbYesNo, "Delete IMP-")
If lDel = vbYes Then
With historyWks
With .Cells(lRecRow, "A")
Application.DisplayAlerts = False
.EntireRow.Delete
Application.DisplayAlerts = True
End With
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With myCopy.Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
Else
MsgBox "Delete Success"
End If
End Sub
modnav
Option Explicit
Sub GoInventory()
On Error Resume Next
Worksheets("MasterData").Activate
End Sub
Sub GoInput()
On Error Resume Next
Worksheets("Input").Activate
End Sub
Sub GoInventory()
On Error Resume Next
Worksheets("MasterData").Activate
End Sub
Sub GoInput()
On Error Resume Next
Worksheets("Input").Activate
End Sub
modviewdata
Option Explicit
Sub ViewLogFirst()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
Set rngA = ActiveCell
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
.Range("CurrRec").Value = 1
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
inputWks.Range("OrderSel").Value = .Range("D5").Value
rngA.Select
End With
Application.EnableEvents = True
End Sub
Sub ViewLogUp()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
Set rngA = ActiveCell
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec > 1 Then
.Range("CurrRec").Value = lRec - 1
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
inputWks.Range("OrderSel").Value = .Range("D5").Value
rngA.Select
End If
End With
Application.EnableEvents = True
End Sub
Sub ViewLogDown()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
Set rngA = ActiveCell
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec < lLastRec Then
.Range("CurrRec").Value = lRec + 1
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
inputWks.Range("OrderSel").Value = .Range("D5").Value
rngA.Select
End If
End With
Application.EnableEvents = True
End Sub
Sub ViewLogLast()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
Set rngA = ActiveCell
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
.Range("CurrRec").Value = lLastRec
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
inputWks.Range("OrderSel").Value = .Range("D5").Value
rngA.Select
End With
Application.EnableEvents = True
End Sub
Sub ViewLogFirst()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
Set rngA = ActiveCell
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
.Range("CurrRec").Value = 1
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
inputWks.Range("OrderSel").Value = .Range("D5").Value
rngA.Select
End With
Application.EnableEvents = True
End Sub
Sub ViewLogUp()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
Set rngA = ActiveCell
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec > 1 Then
.Range("CurrRec").Value = lRec - 1
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
inputWks.Range("OrderSel").Value = .Range("D5").Value
rngA.Select
End If
End With
Application.EnableEvents = True
End Sub
Sub ViewLogDown()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
Set rngA = ActiveCell
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec < lLastRec Then
.Range("CurrRec").Value = lRec + 1
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
inputWks.Range("OrderSel").Value = .Range("D5").Value
rngA.Select
End If
End With
Application.EnableEvents = True
End Sub
Sub ViewLogLast()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim rngA As Range
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("MasterData")
Set rngA = ActiveCell
With historyWks
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
lLastRec = lastRow - 1
End With
With inputWks
.Range("CurrRec").Value = lLastRec
lRec = .Range("CurrRec").Value
lRecRow = lRec + 1
historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 11)).Copy
.Range("D5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
inputWks.Range("OrderSel").Value = .Range("D5").Value
rngA.Select
End With
Application.EnableEvents = True
End Sub
এতে সদস্যতা:
পোস্টগুলি (Atom)