Data input form to be used daily, with data storage

MattJC7

New Member
Joined
Jul 7, 2020
Messages
7
Office Version
  1. 2016
Platform
  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,
Matt

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
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:
VBA 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


1594716148509.png
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

MattJC7

New Member
Joined
Jul 7, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hope no one minds me giving this a bump. Thanks, Matt.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,829
Messages
5,627,143
Members
416,223
Latest member
RichardHell

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 MrExcel.com.
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 "mrexcel.com".
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
Top