Data input form to be used daily, with data storage


New Member
Jul 7, 2020
Office Version
  1. 2016
  1. Windows
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,

Data input code, with only one ward's input code:
VBA 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
Cells(1, 1).Select

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

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

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:
VBA Code:
Sub Clear_data()
    Selection.Value = "0"
    Selection.Value = "Not assigned"
    Cells(1, 1).Select
End Sub

Last edited by a moderator:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back