Copy an Entry to a Sheet

O

oskar

New member
Joined
May 10, 2013
Messages
24
Reaction score
0
Points
0
Location
Montreal, Canada
Excel Version(s)
Office 2013
I'm using Excel 2002, SP3 and I have a workbook with 10 sheets

The 1st sheet is an entry form where I type various activities (including the date) and the rest contain the summary of the various activities and are named Design, Materials, Purchasing, etc. All sheets have only 2 columns (Date and Description)

What I would like to do is when I enter an activity on the first worksheet which starts lets say with the word "Design" it should be copied to the Design sheet, and if the 1st word is Purchasing it should be copied to the Purchasing sheet, and so on

If lets say the Design sheet has earlier entries, the copied entry should be in the next empty row or it could be in the 1st row and I can manually sort the entries in chronological order

What would be the required formula?
 
You would need to make a macro for this. How well do you know the VBA?
It isn't particularly hard to do what you want on this.
simply have a macro you run to check your activity name "Design" for example and then copy the desired information to the correct worksheet name.
appending data to the bottom of a list "the next empty row" is fairly easy to get as well.

If you could attach a sample workbook with the appropriate sheet names and cells you want to be copied, that would be helpful for people to give you a more precise answer.

Simi
 
Thanks Simi for the help

I'm attaching here a sample file of what I would like to do

I'm retired now and my VBA / Macros knowledge is pretty rusty but I can manage basic commands. However I have this formula =IF(LEFT(B349,3)="GAS",IF(D349="",-C349,-D349),"") on my cost control sheet and I thought what I'm after may be something similar; instead of coping a value to a column, it will copy text to a sheet

I have tried various alternatives to the above formula but haven't been successfulView attachment Sample10.xls
 
It would be much better to have one sheet for the data, with an extra column to specify the activity. The way you suggest is likely to be problematic.

This code detects the sheet name from the first word in the cell of Column B. I have used Double_Click as the trigger.
Code: 
Option Explicit


'---------------------------------------------------------------------------------------
' Procedure : Worksheet_BeforeDoubleClick
' Author    : Roy Cox (royUK)
' Date      : 11/05/2013
' Purpose   : Copy entry to specific sheet  based on first word in Cell
' Web Site  : www.excel-it.com
'---------------------------------------------------------------------------------------
'
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'only work on Column B
    If Target.Column <> 2 Then Exit Sub
    Dim sShtname As String
    Dim iX As Integer
    Dim NextRw As Long


    On Error GoTo exit_proc
    iX = Application.WorksheetFunction.Find(" ", Target.Value) - 1
    sShtname = Left(Target.Value, iX)


    With Worksheets(sShtname)
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        Target.EntireRow.Copy .Cells(NextRw, 1)
    End With


    MsgBox "Entry pasted to " & sShtname, vbInformation, "Success!"
exit_proc:
    MsgBox "Entry could not be copied. Please Check the description", vbCritical, "Check input"
End Sub
 
Thanks Roy for your time but could not make it work, I copied the code to a new module and it says line is not executable

Notice in your code that there is nothing about the name of my sheets so I dont know how should work

I will give another try and see if I can make it work

Appreciate your time
 
Te code should not be a Standard Module, it is a worksheet event code.
 

Attachments

  • Sample10.xls
    74 KB · Views: 34
I personally would use a userform for input. A combobox for the category, a textbox for the description and command button to accept what you've input.

Including file for quick example.
 

Attachments

  • EntryFormSample.xls
    67 KB · Views: 22
It says quick example not perfect example.

Why not put Exit Sub ahead of check input?
 
I wasn't commenting on the code, but the need for the UserForm and the Input Sheet. I can't see a reason for both.

If you are touchy about your code then yes it can be much more efficient but that was not what I said
 
Roy, my post was only an idea that the OP could look at for consideration. I'm not into the 1-ups game, but that's the way your reply came across. Hey man, I've learned more from you on the Excel forms than you can imagine and your reply looked like nothing but a David and Goliath thing.

Sorry for the way you feel about my reply. What I should have said is that the original entry sheet provides a quick time-line reference.
 
NoS said:
I personally would use a userform for input. A combobox for the category, a textbox for the description and command button to accept what you've input.

Including file for quick example.
Click to expand...

Thank you all for the help

Although I prefer the Entry sheet for simplicity your idea Nos about the user form is quite good and I have to investigate its use

I tried to find how you add categories but I was not able; but I did find how to format the form and to add a Ctrl+Z key to open the form as needed
 
As I said, I wasn't commenting on the code at all. Merely that I could see no reason for a UserForm & an Input Sheet. I hadn't even looked at your code until you replied.
 
royUK said:
Te code should not be a Standard Module, it is a worksheet event code.
Click to expand...

Sorry Roy but I dont understand the attached file neither where a standard module is located and how to activate it / edit

I would appreciate it if you have the time to let me know how it works

As I said this method is better but if I cant make it work, the suggested user form is a very good alternative
 
Hey guys, no need to argue who is providing a better answer or not, you both provide an excellent help and its up to me to decide which I prefer regardless if one is better than the other

It is all about my limited knowledge and with your help I will eventually get what I want

Cheers
 
I wasn't arguing about the answer, only that both would not be necessary.

All the code is in the example that I posted and you should be able to see where it is.

In my earlier post i said that all the data would be better in the one sheet. From there I would have a UserForm to enter data.
 
You must log in or register to reply here.
Back
Top