+ Reply to Thread
Results 1 to 41 of 41

Excel vba copy data from general sheets and organize them in separate sheets.

  1. #1
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Question Excel vba copy data from general sheets and organize them in separate sheets.

    Hello,

    We have been using this excel is sheet in our business manually, so in the process we commit a lot of errors. It will be great to be able to enter all data into "Sheet 1" and "Sheet 2" for example, and then excel automatically takes this data and organize it in the rest of the sheets as we need it in this case, separated by banks accounts.

    I hope someone of you can help me learn what should I do here, what function do I need or maybe what vba code should I use. However, I do not have good knowledges on visual basic.

    Regards
    Daniel
    Attached Files Attached Files

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    You do not have a Sheet1 and Sheet2 in your file. Are you talking about the 3 green tabs?

    Also, which if the field which contains the Bank account name? Is it column C?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Yes, when I meant Sheet 1 and sheet 2, I refered to the 3 green tabs. I want to be able to fill information in those 3 tabs only, and automatically fill info in the gray tabs. If not, I will have to enter information 2 or 3 times each time.

    Thats correct, in Sheet "Ingreso" is column C.

    So Banco Ingreso --- Bank that received money.
    Banco Egreso -- Bank from which we will send the money.

    thanks,
    Daniel

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    What should go into the tab titled "Balance" ?

  5. #5
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Balance will be gathering all the information. As first sheet will gather incoming money and second sheet will gather outgoing money, "Balance" will recopilate the 2 transactions so that you can see the total amount of liquid money in the the current date.

    Regards
    Daniel

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Do you want the "Balance" sheet to be populated by the macro?

    Also, the "Ingreso" sheet is not in the same format as the other 2. Is that fine? Will it always be having 4 columns while the "Egreso" sheet has 6 columns?

  7. #7
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    that is right, "balance" sheet I want to be populated by ingreso and egreso sheets. Yes, ingreso doesn't have description or category as it is always the same sale.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    So you do not want the macro to populate the other sheets? Only the balance sheet should be populated?

  9. #9
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Yes the other sheets too. For example and incoming money enters into the bank "Oficina", and the user will fill the info in "Ingreso". Then, I want excel to put that information in Balance, as well as the bank "oficina". With this, I will be able to know how much the current balance of the business, and the current balance and detailed operations of each bank account.

    Regards
    Daniel

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Ok possibly the last question.

    Suppose today you populate the data into the green sheets and then run the macro. The data will get copied into the respective sheets. Then tomorrow, you want to populate more data. Will you be clearing the 3 sheets and putting in fresh data or will you put data in the next available row of the 3 sheets?

  11. #11
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    I will put the data in the next available row of the 3 first sheets.

  12. #12
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Do you think you can help me with this?

    Regards
    Daniel

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    I was working on your code when i got stuck here -

    In the Tinterna sheet, there are 2 columns C & D. So where should the data go from this sheet? Into the sheet names given in column C or in column D?

  14. #14
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Tinterna will be to record internal transfers, so we can move money between our own accounts. It will not affect the Balance sheet, as no money is coming in or out, it is just moving. However, it will change money between banks account. So banco egreso means money will go out of that bank account into banco ingreso, reflected in the sheet of each account.

    Regards
    Daniel

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    So whatever details are present in Tinterna dont need to go into the "Balance" sheet right? But if i need to copy paste the data into the other sheets for the respective accounts, which column holds the sheet name? Column C or D?

  16. #16
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Both. For example, in Tinterna - Banco Egreso = "Mercadopago", and banco ingreso = "Oficina", amount 2000. That will means that in bank account Mercadopago, 2000 will go out from the account, and in bank account sheet "Oficina" 2000 will go into the account.

    In this way, money was transfer between accounts. When I enter then in Mercadopago, there will be no more those 2000, as they will be in Oficina.

    Best REgards,
    Daniel

  17. #17
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    So the records should go only into the sheets stated in column D?

  18. #18
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Into the sheets stated in Column C and D, as sheet in column C will register an "Egreso" and sheet in column D will register an "Ingreso"

  19. #19
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    One last question - should the information from Tinterna go into the Balance sheet?

  20. #20
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    It can go in balance sheet or not. Le me explain, if current balance in balance sheet is for example 1500USD, and I make an internal transfer from "mercadopago to oficina", the balance of the business should still be 1500USD. That is because no money have gone out of our accounts, just ttransfered between them. So, from that point, is not necessary to appear in balance. However, it can also appear in balance sheet as an outgoing "Egreso" and then as an incoming money "Ingreso". In that way, an internal transfer of 1000 will be 1000-1000 = 0, and does not alter the balance.

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Ok. Try this code - instructions are given below the code on how to use it -
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button

  22. #22
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Thanks!, I have inserted and it is starting to work, however dome numbers does not appear well. I attach the file so you can see. For example in "Banesco" first transaction has an incoming and an outgoing at the same time. which can not be. the "ingreso which is 1000 is good" but the number in "egreso" 1760 corresponds to the reference code G102 which is below along with also wrong numbers.

    REgards
    Daniel
    Attached Files Attached Files

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Try this updated code
    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    It says script out of range.

  25. #25
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Which line is highlighted?

  26. #26
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    lastrow = Worksheets("sname").Range("A" & Rows.Count).End(xlUp).Row

  27. #27
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Oops am sorry. You will see multiple such rows in the code. Please remove the " before and after the sname. It should only be worksheets(sname).

  28. #28
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Wow its incredible what you did! Thank you very much! I know that is a lot of effort!

  29. #29
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    What should I put in the code so that in Sheet "Balance" I also can see the bank into which the transaction is registered. AS the field Bnaks remains in blank right now.

  30. #30
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Which is the column which contains that info?

  31. #31
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Don't worry its ok without that. When running the macro, when I go to a bank account sheet it has frist incoming, then outgoing and then internal transfer? If I want them to be in order by date, should I insert a filter and each time running the macro organize filter as descending date? or can I put a code in the macro to do it automatic?

    Regards

  32. #32
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    You do not need filter. You need to include the sort code. Do you want all sheets (except the 1st 3) to be sorted by date?

  33. #33
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Yes all of them except the first 3

  34. #34
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Updated code
    Please Login or Register  to view this content.
    Last edited by arlu1201; 01-22-2013 at 09:20 AM.

  35. #35
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    This is what I get: the sort reference is not valid. make sure it is withing the data you want the sort, and the first sort by box isnt the same or blank.

    Highlited line : .Apply (at the end)

  36. #36
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Sorry a typo in the code. I have edited the code above.

  37. #37
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    I have tried it, but the manu bar went out of position. I attach an image
    Attached Images Attached Images

  38. #38
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    But the code works perfectly, I never thought I would have found someone to help me do this!

  39. #39
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    I have corrected the code - sorry i didnt realise it was creating a problem -
    Please Login or Register  to view this content.

  40. #40
    Registered User
    Join Date
    01-20-2013
    Location
    caracas
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Thank you very much!, it worked out perfectly!

  41. #41
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Excel vba copy data from general sheets and organize them in separate sheets.

    Am glad it worked for you.

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1