Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Copy information to bank reconciliation sheet (XP)

    Good afternoon from a very hot New Zealand
    I have written a spreadsheet to calculate 2 monthly tax returns. It works great. However, now I've been asked to create a bank reconciliation sheet.
    Can someone help me with a macro that does the following:
    When I type a B in the Bank column (column [img]/forums/images/smilies/cool.gif[/img] that the Date - Description - Credit - Debit info is automatically copied into the Bank sheet.
    Having the date in chronological order would be a bonus, but not sure if that is possible.
    I can't attached the spreadsheet because the file is far too big.
    So from the "Out" pages (6 of them) the info goes into column A - B - C
    and from the "In" pages (6 pages as well) it goes into A - B - D

    Thanks for all the help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    Could you create a small stripped down copy and attach that? Leave only one "In" sheet, one "Out" sheet and the "Bank" sheet, and remove most of the data, leave just enough to give us an idea. You should replace private or sensitive information with dummy data. Thanks.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    It was hugely stripped of all info and still far too big.
    So I made it a PDF file which is the only way of getting it to you
    Page 1 is the "Out" sheet with the columns listed in Red
    Page 2 is the "In" sheet with the columns listed in Red as well
    and Page 3 is the Bank sheet in which I would like the information

    There are 6 In sheets and 6 Out sheets.

    Thanks heaps for all your help

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    I cannot test anything on a PDF file.
    If you copy the information you display in the PDF file into worksheets in a new workbook, it can't be very large. Zipping it should reduce the size even further.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    Attached a very basic excel file. I started from scratch which kept the size low.

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    See if the attached workbook does what you want. Each "in" and "out" worksheet has a Worksheet_Change event procedure that calls the procedure FillBank in the Module1 module.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    I feel pretty silly but I have no idea how to get this info into my own workbook. I tried but no success.
    Can you give me some easy step by step instructions ??
    Sorry about this.. Don't like saying I don't know certain things

    Thanks so much

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    Right-click the In April-May worksheet tab in the workbook I attached.
    Select View Code.
    Copy the code you see to the clipboard.
    Right-click one of the "In" worksheet tabs in your own workbook.
    Select View code.
    Paste the code into the module.
    Repeat for the other "In" worksheets.

    Now do the same for the "Out April-May" worksheet in my version and the "Out" worksheets in your workbook.

    Finally, drag the Module1 module (in the Visual Basic Editor) from my version to your workbook.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    Works great.. Your instructions were easy to follow.
    But... There is always a 'but'
    The Bank Column is column B and when I fill out the columns and I enter the information into columns a - b - c - d - e etc it doesn't work. It only works if I enter the "B" into column B at the end.

    Thanks anyway for all your help

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    You asked
    <hr>When I type a B in the Bank column (column [img]/forums/images/smilies/cool.gif[/img] that the Date - Description - Credit - Debit info is automatically copied into the Bank sheet.<hr>
    so that is what I wrote. It can be changed, but there must be something to trigger the copying - you don't want a line to be copied each time you type something in any cell. The alternative would be to use a command button or custom toolbar button, but then the user has to initiate the copying. What would you prefer?

  11. #11
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    Hans,
    I was trying to get this to work--intrigued by the question and solution. But I can't get anything to happen.
    Am I right? I am supposed to go into the "In" sheet and type "B" in the B column in the row that I have entered something last and it will copy that information into the "Bank" sheet?

    Nevermind. You said to put a "B" in the column, and when I typed a "B" it automatically filled in with "Bank" from the header row. Then I tried "b" but that was lower case. I guess it pays to follow the instructions!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Copy information to bank reconciliation sheet (XP)

    You can make the comparison case insensitive by changing

    If oCell = "B" Then

    to

    If UCase(oCell) = "B" Then

Posting Permissions

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