Dispatch Log VBA

[Timmer7]

Hi,

I'm getting back in the saddle with excel again and was hoping I could get help with some VBA for what I want to do with our dispatching capabilities.

Please see images. I have a source sheet with data which is to be copied to my Dispatch_Data sheet when in the column A list the proper code is chosen; the copied part would go into columns B:K or however many columns I needed from the source sheet; column B (2) would also put in the current date & time (military). My goal is add multiple lines to the source sheet as I go here. If anyone's able to help me with this, much thanks in advance!

Sincerely,

Timmer

Dispatch Log.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6	Code Red	5/25/2024 14:00	Location:	Officer:	Details:	Device:	Time Completed:		Misc:
7	Patient Assist	5/25/2024 18:00	Location:	Caller:	Details:	Other Inv:	Time Completed:		Misc:
8	Code Pink
9
10
11
12
13
14
Dispatch_Data

Cells with Data Validation
Cell	Allow	Criteria
A6:A14	List	=Calls

Dispatch Log.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
5
6	Code Red	5/25/2024 14:00	Location:	Officer:	Details:	Device:	Time Completed:		Misc:
7	Patient Assist	5/25/2024 18:00	Location:	Caller:	Details:	Other Inv:	Time Completed:		Misc:
8
9
Source

 

[mumps]

in the column A list the proper code is chosen

Column A of which sheet?

column B (2) would also put in the current date & time (military)

It appears that this information is already in column B of the Source sheet. Please clarify in detail referring to specific cells, rows, columns and sheets using an example from your data.

 

[Logit]

One method is to use a UserForm with TEXT fields matching the entries of the cells on your Dispatch Log. The UserForm is where you would enter your data then use
a CommandButton on the UserForm to send the data there to the Dispatch Sheet for storage. Attached is a small example project using a UserForm. Study the design
and formatting then edit it to match your project.

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Download sample file : Internxt Drive – Private & Secure Cloud Storage