Spreadsheet dataSolved/Closed

Question

Hi everybody.

I am trying to develop a large data input spreadsheet which will have circa 20 operators data input on a daily basis. (approx 150 enties for each operator)

I am looking at if I should have one sheet for all input which will then be copied to individual ops for analysis. Or if it is more efficient to input each operator & have a macro copy all data to the master sheet?
Also please advise how I tell the macro to find next empty cell when pasting ongoing data?

When finished it will be for use by a work colleague who needs it to be 'simple stupid'

Regards

Ankharm

TrowaD · Answer

Hi Ankharm,

Let me see if I understand you the right way.

Situation 1:
You want 1 big sheet where a column is used to input operator name. Copy rows of data to the correct sheet (operator name = sheet name).

Situation 2:
Each operator uses his own sheet (operator name = sheet name). Copy rows of data from each sheet and paste it to a single (big) sheet.

Both situations can be realized. So pick one.

Then provide some sample data containing:
2 or 3 "Operator" sheets with some data.
The "big" sheet with some data.

Use a filesharing site (www.speedyshare.com) to upload your file and post back the download link.

Pasting to the next empty cell is mostly done in this way:
Range("A"&rows.count).end(xlup).offset(1,0)

Best regards,
Trowa

ankharm · Answer

Hi Trowa

Much appreciate your response.

I will send the file as suggested and advise location asap.

since posting I have continued working on the project and you will find I have created a sheet for each working day of the month for input. Each day will have circa 70 entries.

I have attempted to record a macro, but whilst it works on first run, it fails thereafter. You will see the code and my fumbling first attempts.

To reiterate my goal: Following each days input, the macro must sort the data by operator, then copy & paste into the operators sheet. on subsequent days it needs to find the next available row in the operators sheet to paste the next days data, thus building a months data history for each operator. The workbook has a sheet for each operator denoted by their initials.

I am sure with your experience you will advise the most time efficient way to develop the workbook.

Once again, I appreciate your offer of help.

Regards

Ankharm

ankharm · Answer

Hi Trowa

the link for the file is:

http://speedy.sh/4weyf/Macro-Project.xlsm

Didn't realise it was that easy.

Regards

ankharm

TrowaD · Answer

Hi Ankharm,

First we need to determine when you want to run the macro.

I've got three options for you to choose from.

Option 1:
Run the code at the end of the month.
This way macro only needs to run once a month.

Option 2:
Run the code at the end each day.
Or after a few days. The idea is that the selected sheet will be handled. You can do a few sheets after each other if needed.

Option 3:
Data is handled immediatly and automatically.
The idea here is that when you enter your data in a "day" sheet, you enter the operators initials lastly. When you confirm the initials (by hitting enter), the row of data will be copy/pasted to the appropriate operator sheet.

Let me know what your preference is or if you have questions about the options.

Best regards,
Trowa

ankharm · Answer

Hi Trowa

The 3rd option sounds ideal.
it sounds as though it will be the least problematic for the input clerk.
And they can analyse any operator at any time with current data.

was the sheet I sent too cumbersome, or can we make it work?

regards

Ankharm

TrowaD · Answer

Hi Ankharm,

The file is ok, but I noticed that when I'm on a Day sheet, hit End and push down I will get to row 700. So I used rows 2:699 as possible entry rows. Meaning you won't enter rows of data after row 699.

Copy this code into each of the Day sheets:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2:C699")) Is Nothing Then Exit Sub
Dim opID As String
opID = Target.Value

Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Copy

On Error GoTo EM
Sheets(opID).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

Application.CutCopyMode = False

Exit Sub
EM:
MsgBox "There is no sheet called " & opID & " !", vbExclamation
End Sub
You will get a warning when the Operator's ID hasn't got his own sheet yet, or when you make a typo.

This code will work for any upcoming entry's.

To handle the data you have already inputted I have created the following code:
Sub HandleExistingOpID()
Dim ws As Worksheet
Dim lRow, x As Integer

For Each ws In Worksheets
    If Len(ws.Name) = 5 Then
        x = 1
        Do
            Sheets(ws.Name).Activate
            lRow = Range("C" & x).End(xlDown).Row
            If lRow > 698 Then GoTo NextWS
            x = x + 1
            Range("C" & x).Value = Range("C" & x).Value
        Loop Until x = lRow
    End If
NextWS:
Next ws

End Sub
Run this code from a standard module. The code basicly loops throught the Day sheets and re-enters the operator ID's thus activating the first code.

Best regards,
Trowa

ankharm · Answer

Hi Trowa

Many thanks for your time, and I really appreciate your input.

I'm afraid I do have a glitch:
I have input the macro in a day sheet, and she runs ok BUT..
As the op id is in column C, the macro runs as soon as the op is entered, but that is before the actual data numbers have been entered. When checking the target sheet it only contains the data entered in first three columns.
Being as I have only just started on my journey with VBA, I am struggling to see where in the code it targets the column for the ID of the op.
My assumption is that I should move the op id input to the last column, but how do I re-write the macro to find the ID target?

Thanks for these invaluable lessons.

Regards

Ankharm

PS: I'm in UK, so I guess we have time lag between responses.

ankharm · Answer

Hi Trowa

I've solved the issue, so many many thanks for your valuable help

Probably saved me at least a month of anguish.

Regards

Ankharm

TrowaD · Answer

Hi Ankharm,

That's why I told you in my april 11th post that the Operator ID should be filled in last.

But it is always nice to find your own solution.

Best regards,
Trowa

Spreadsheet data

9 responses

Similar discussions

Subscribe To Our Newsletter!