1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Conditional Selective Cell Copy

Discussion in 'Business Applications' started by ramwire, Jul 24, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. ramwire

    ramwire Thread Starter

    Joined:
    Jan 17, 2006
    Messages:
    14
    Hello,

    I need help with Excel (2003 if it helps).

    I have an invoice list workbook with sheets for each month. In each there are multiple columns of which I'm interested in Invoice# / Client name / Amount. Also the column Start Date for the formula use only.

    Here's what I need.

    In a new workbook, I need to have multiple sheets for each month of the year and I need excel to look at the Start Date of the invoice workbook and list all clients that have a start date in say October for example in the October sheet of this new workbook.

    Mind you they will be spread over many months, for example a customer could've made an order and been invoiced in March so his invoice will show in the March sheet of the invoice workbook, but if his start date is October, I want it to show in the october sheet of the new workbook.

    I really hope it was clear enough. Please do ask for any clarifications needed.

    Hope someone can help.

    Cheers.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Not really clear enough. Take these statements:

    In a new workbook, I need to have multiple sheets for each month of the year

    and

    but if his start date is October, I want it to show in the october sheet of the new workbook.

    First statement suggests there are multiple "October" sheets in the new wb -- second statement suggests there's only one "October" sheet in the new wb.

    Your best bet is to upload a sample file with some dummy data including "before" & "after" sheets.
     
  3. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Are you trying to get individual line items or a total for each month? A pivotable can give you a summary as well as break it down by month as individual sheets in a matter of minutes. Can you post the Excel spreadsheet so I can take a look at it as well?
     
  4. ramwire

    ramwire Thread Starter

    Joined:
    Jan 17, 2006
    Messages:
    14
    Sorry for the late reply guys.

    And thank you for your suggestion. I have created 2 samples, one for the invoice list and another for my desired new workbook (call it sales report).

    BTW, I meant 1 sheet per month. I guess I should've been clearer.

    I'm attaching the two files. I hope with these and my previous explanation it'll be clearer. And I promise I'll keep a closer look on this thread this time :)
     

    Attached Files:

  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I'm still lost. "Start Date" for "Michael" is "02/03/2007" (Jan tab). Assuming dates are not US format, why would he "go to" Feb tab in the other book?

    :confused:
     
  6. ramwire

    ramwire Thread Starter

    Joined:
    Jan 17, 2006
    Messages:
    14
    Oh dear, I guess different date formats are causing this issue now.

    I see Michael's start date as 3/2/2007 (read: 3rd of February 2007).

    That's why in the report WB Michael is listed in Feb.

    In other words, Michael made an order, was invoiced for it and partly paid for it in January but he only wishes to start his service in February. So in the "Invoice List" WB he appears in the Jan sheet while in the "Sales Report" WB he appears in Feb since his service start date is in February.

    Hope that clears it up further.

    Thanks for your continued efforts.

    Cheers.
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    In my opinion, you don't need multiple sheets in "invoice-list" to begin with -- autofiltering would be a better bet. But that's up to you.

    Re: the date formats -- assuming you have >0 experience with macros (AKA VBA), with D5 of Jan (invoice-list) selected (02/03/2007), what do you see when you run the following?

    Sub test()
    MsgBox Format(ActiveCell.Value, "mmm")
    End Sub


    (I see "Mar")
     
  8. ramwire

    ramwire Thread Starter

    Joined:
    Jan 17, 2006
    Messages:
    14
    Ok you've helped me see a big problem I wasn't aware of before. You're right, I see "Mar" too which means Excel is understanding this as 2nd March instead of 3rd February which is what I intended. The original sheet is huge and the dates are all following the same style I used in my sample, as in 3/2/2007 for 3rd Feb 2007.

    Strangely, Excel is still showing me 3/2/2007 in both the cell and the formula bar.

    I still don't know how to work this date issue out but for now I would appreciate a way of being able to look for a certain value in a specific column and if a match is found then cells on the same row of the match would be copied to a sheet in a different WB.

    Cheers.
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Excel shows me 02/03/2007 in the cell and the bar. The cells are formatted as General, that might be an issue.

    In the attached there's a very small macro. From any ws, press ALT+F8 then double-click test.

    All it does is scan the cells in the No. field (A4 & down) & "report" the month in the Start Date field. When I run it (for the Jan tab), I see:

    Jan - Mar - Mar - Aug - Nov

    Obviously this is the basic idea -- to evaluate the month for each record to know which tab in the other book to copy to. But the month issue will need to be resolved.
     

    Attached Files:

  10. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Do you want the data "Copied" to another worksheet or another workbook? I would recommend and autofilter like bomb was saying or you can do an advanced filler on the other worksheets that will only pull in the values for that month. This is easily feasible, so let me know if you are interested in having the invoice list and month sheets tabs in one Workbook and I will show you hot to set it up.
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Wouldn't some VBA along the lines of if mm=01, [Filenamebook2]:Sheet January, if mm=02, [Filenamebook2]:Sheet February, etc - I am sure you would want to locate the cell and row in the new book also -be something you could do here?
     
  12. ramwire

    ramwire Thread Starter

    Joined:
    Jan 17, 2006
    Messages:
    14
    Hi guys, thanks so much for your continued efforts to help me out. I'm currently working on trying to fix the date issue bomb #21 helped point out. I'll get back to you all once I reach somewhere in that effort.

    BTW, bomb - your macro is a major help in this task. It's odd how the months where the date is higher than 12 are correctly identified but those with 12 or less have the month switched with the date.

    Cheers.
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Bomb's macro works well, as long as the dates are all formatted to match. See attached.
     

    Attached Files:

  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Thanks slurpee. (y)

    ramwire, see the attached books with modified code. The sheets in "sales-report2" have been wiped. With both books open and "invoice-list2" active, run the code ("test"). It should cycle the "invoice-list2" sheets, copying the records to the relevant tab where possible. By which I mean, Invoice 220807 can't be copied because there's no "Aug" tab in "sales-report2", therefore it gets skipped (by On Error Resume Next).

    HTH

    Sub test()
    If ActiveWorkbook.Name <> "invoice-list2.xls" Then Exit Sub
    Sheets(1).Select
    For i = 1 To Sheets.Count
    For Each Cell In Range("A4", Range("A" & Rows.Count).End(xlUp))
    On Error Resume Next
    Cell.Resize(, 3).Copy _
    Workbooks("sales-report2.xls").Sheets(Format(Cell.Offset(, 3), "mmm")).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Cell.Offset(, 4).Copy _
    Workbooks("sales-report2.xls").Sheets(Format(Cell.Offset(, 3), "mmm")).Range("A" & Rows.Count).End(xlUp).Offset(, 3)
    Cell.Offset(, 7).Copy _
    Workbooks("sales-report2.xls").Sheets(Format(Cell.Offset(, 3), "mmm")).Range("A" & Rows.Count).End(xlUp).Offset(, 4)
    Next Cell
    Sheets(i + 1).Select
    Next i
    End Sub
     

    Attached Files:

  15. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    pretty slick work, bomb! (y)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/599743

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice