Auto Copy Data from one worksheet to another

Discussion in 'Excel Programming' started by jsphcwl36@gmail.com, May 7, 2014.

  1. Guest

    I have Sheet 1 containing columns and rows of chronologically entered transactions.

    I would like certain data in this sheet that conforms to certain criteria to automatically appear in sheet 2 as and when that data is entered in sheet 1.

    Is VBA is the route to go, and if so, could someone suggest the code that might be used?

    The Column headings in Sheet 1 are:

    DATE DETAILS DEBIT CREDIT

    The criteria is to copy into sheet 2 only rows containing:

    AV
    CL
    SP

    from the DETAILS column.

    I hope all this makes sense and any help would be much appreciated.

    Joe
     
    , May 7, 2014
    #1
    1. Advertisements

  2. GS Guest

    Hi Joe,
    I do something similar whereby I have a "Simple Bookkeeping" project
    which contains a 'Summary' sheet that uses formulas to pull data from 2
    other sheets based on the 'Month' value in the 'Transaction Date'
    column of those other sheets. This 'Summary' sheet is configured same
    as a Profit/Loss report with columns for each month of a calendar year,
    YTD, %Revenue, and %Expense. The bottom of the table calcs Qtr totals.

    The 'Income' and 'Expense' sheets do not require entries be in
    chronological order, but right-click menus let me insert rows where
    needed above or below the active cell. This project requires 'helper'
    cols/rows be used as indices for formulas. Also, the month columns need
    to be in the same position on all 3 sheets, and so the cols used for
    entry detail on Income/Expense are hidden on Summary. These sheets
    parse their transaction values into their own month cols, which in turn
    are used as refs for the month cols on Summary.

    The 'Summary' sheet pulls values for each 'Chart of Accounts' item into
    separate month columns, and displays sub account values in its 'detail'
    view. The CoA item values are parsed to each month col across their
    respective row. The only VBA used on this sheet is for handling the
    expand/collapse of rows for detail/summary view of individual,
    selections (groups), or all CoA items. The standard Group & Outline
    markers also are accessible, and this sheet is protected so it's 'Read
    Only' at runtime.

    Local scope defined name ranges are used on all sheets so formulas are
    identical in every column. For example...

    Summary formula used for all month cols:
    =PeriodExpense (all top level CoA items)
    =PeriodExpenseDetail (all subitems for each top level item)

    Income/Expense formulas used for all month cols:
    =PeriodAmount (each transaction by month)
    =CurrencyTotal (each foreign currency total by month)
    =SUM(ThisColumn) (Grand total for each month)
    {=PeriodOutstanding} (all total unpaid transaction amounts)
    {=PeriodTax1} (total Tax1 on all transactions)
    {=PeriodTax2} (total Tax2 on all transactions)

    ...where even the formulas have defined names to simplify working with
    them. The point of this rather long-in-tooth explanation is that it's
    usually best to leave the things Excel doesn't do to VBA because
    Excel's built-in functions will outperform VBA routines by orders of
    magnitude 'in most case'!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 8, 2014
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.