Copy an Entry to a Sheet

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?
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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
 

oskar

New member
Joined
May 10, 2013
Messages
24
Reaction score
0
Points
0
Location
Montreal, Canada
Excel Version(s)
Office 2013
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
 

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
most versions
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
 

oskar

New member
Joined
May 10, 2013
Messages
24
Reaction score
0
Points
0
Location
Montreal, Canada
Excel Version(s)
Office 2013
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
 

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
most versions
Te code should not be a Standard Module, it is a worksheet event code.
 

Attachments

  • Sample10.xls
    74 KB · Views: 34

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
It says quick example not perfect example.

Why not put Exit Sub ahead of check input?
 

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
most versions
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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.
 

oskar

New member
Joined
May 10, 2013
Messages
24
Reaction score
0
Points
0
Location
Montreal, Canada
Excel Version(s)
Office 2013
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.

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
 

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
most versions
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.
 

oskar

New member
Joined
May 10, 2013
Messages
24
Reaction score
0
Points
0
Location
Montreal, Canada
Excel Version(s)
Office 2013
Te code should not be a Standard Module, it is a worksheet event code.

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
 

oskar

New member
Joined
May 10, 2013
Messages
24
Reaction score
0
Points
0
Location
Montreal, Canada
Excel Version(s)
Office 2013
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
 

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
most versions
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.
 
Top