Data input form to be used daily, with data storage

[MattJC7]

Dear all,

After my previous post yielded excellent optimisations, I'm back again. I'll try to keep this brief because I've just had surgery and can't sit for long at the PC. Although I'm off work, these spreadsheet problems won't leave my head!

Issue: We currently track daily workload and staff allocations using word documents for each team. This means we're losing our workload data. Each team comprises of separate wards at the hospital.

My solution: Created a spreadsheet with the data entry fields from the word document as a sheet on excel. When pressing submit data this copies and pastes data to relevant sheets for each individual ward for data analysis. I left all of the wards present so that we can change wards between teams fairly easily and each team just have to filter out by "cluster" - column A. Note: I've blanked out ward names and hidden all sheets with ward names.

Problems with this:

1) Could the "copying and pasting" method be optimised? It takes 5 seconds or so to complete. I read online that there is another method for transferring data but I can't find it again now!
2) There is a separate document for each team to input and store data, and each document contains all of the wards and they filter out the ones they don't need. This means each team carries blank data as in step 1, it's copied and pasted. We are limited by a shared drive and not a cloud, so I figured we can't have a centralised Excel database that each team submits to? What if they submit at the same time?
3) I wonder that my code simply isn't optimal, and I'm very much a VBA noob and I would like this to run much smoother.
4) Accurate data input is very much dependent on the wards staying in the correct rows - my solution is to protect the sheet. However, I'm wondering if this is the most optimal solution and is there a better method for data gathering than just "copy and paste this row".

Sorry if this is too verbose. Greatly appreciate any help in optimising this sheet/making it more user friendly.

Kind regards,
Matt

Data input code, with only one ward's input code:

Sub data_input()

Dim String1 As String
String1 = "CDU"
Dim String2 As String
String2 = "DCU"
Dim String3 As String
String3 = "ED"
Dim String4 As String
String4 = "ITU"

'etc for each ward

'Input CDU data
Sheet1.Select
Range("C11:J11").Select
Selection.Copy
Cells(1, 1).Select

Sheets([String1]).Visible = True
Sheets([String1]).Select
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.Value = Date

Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

Cells(1, 1).Select
Sheets([String1]).Visible = False

End Sub

Clear data code:

Sub Clear_data()
    Range("C11:H29").Select
    Selection.ClearContents
    Selection.Value = "0"
   
    Range("I11:J29").Select
    Selection.ClearContents
    Selection.Value = "Not assigned"
    Cells(1, 1).Select
End Sub

 

[MattJC7]

Hope no one minds me giving this a bump. Thanks, Matt.