Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Copy an Entry to a Sheet

  1. #1
    Seeker oskar's Avatar
    Join Date
    May 2013
    Location
    Montreal, Canada
    Posts
    17
    Articles
    0

    Copy an Entry to a Sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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

  3. #3
    Seeker oskar's Avatar
    Join Date
    May 2013
    Location
    Montreal, Canada
    Posts
    17
    Articles
    0
    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 successfulSample10.xls

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    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
    Hope that helps

    Roy

  5. #5
    Seeker oskar's Avatar
    Join Date
    May 2013
    Location
    Montreal, Canada
    Posts
    17
    Articles
    0
    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

  6. #6
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Te code should not be a Standard Module, it is a worksheet event code.
    Attached Files Attached Files
    Hope that helps

    Roy

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.
    Attached Files Attached Files

  8. #8
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    If you are uysing a UserForm why bother with the input sheet?
    Hope that helps

    Roy

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    It says quick example not perfect example.

    Why not put Exit Sub ahead of check input?

  10. #10
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    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
    Hope that helps

    Roy

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •