Consecutive data in different sheet

[rodante]

All help you can give me, I will appreciate.

I have dated cosecutive information in a jounal, Dates, Concept, Doc No. Account Numbers, Figures,etc. Then in another sheets, I have to transfer that data to individual sheet for each account, as a Ledger.

How can I using a formula can I transfer automaticly the data from the general sheet to each individual account sheet in a row consecutive matter?

Example:

Journal
Row Date Concept Doc No Acc No Debit Credit
6 10/20/05 Telephone 567 500-01 56.00

Transfering to:
Individual Account Sheet Acc No 500-01 Telephone
Row Date Concept Doc No Acc No Debit Credit
13 10/20/05 Telephone 567 500-01 56.00

Because this Row is the continuing Row with data filed in previous Rows.

Thanks for all help you can give.
Rodante

[mdmackillop]

Hi Rodante,
Welcome to Vbax
Can you "sanitize" and post a copy of your worbook. You can do this by zipping the file and attach it using Manage Attachments in the Go Advanced page.
Regards
MD

[rodante]

Hi Rodante,
Welcome to Vbax
Can you "sanitize" and post a copy of your worbook. You can do this by zipping the file and attach it using Manage Attachments in the Go Advanced page.
Regards
MD

Thanks for answering

As you requested, here is a example for what I would like to accomplish:

I want to data from Register to be date consecutive in Account sheet without any blank line in that sheet.

I hope this will help to find the right method or formula to use.

Thanks again.

[mdmackillop]

Hi Rodante,
This may be a bit simplistic for what you're after, but I'm not entirely clear on your requirements.
Entering the account number in Column E of Register will look up column A in the sheet named eg. 100-01Cash to find the next clear cell and enter a simple =Register!$A$5 type formula in each cell in the row.
To try, enter 100-01 in cell E6 of Register.
Regards
MD

[rodante]

Hi Rodante,
This may be a bit simplistic for what you're after, but I'm not entirely clear on your requirements.
Entering the account number in Column E of Register will look up column A in the sheet named eg. 100-01Cash to find the next clear cell and enter a simple =Register!$A$5 type formula in each cell in the row.
To try, enter 100-01 in cell E6 of Register.
Regards
MD

Hi mdmackillop:

I appreciate your effort in solving me this problem. Your appreciation for my requirement is not clear because my obscure explanation. What I want is to find a way to automatically every time I registered any data in sheet Register, this data can be transfered to each account sheet of the ledger. The absolute formula above is correct to transfer data for that especific row, but I have to register lots of data in sheet Register with different account numbers and transfered to each account in the ledger in a consecutive row matter. The ledger can contain 100-150 account sheets. I hope this added information can help.
Regards and my total appreciation.

[austenr]

So if I understand your problem correctly, you want to enter info in the sheet called "Register" and then find the sheet with that key data already in it and also put it on that sheet as well?

[rodante]

So if I understand your problem correctly, you want to enter info in the sheet called "Register" and then find the sheet with that key data already in it and also put it on that sheet as well?

Correct?

It is something as a accounting program doing it in Excel with different sheets for each account. So, as data is registered in the Register Sheet the data is transfered to each corresponding sheet account row consecutivly.

Thanks for your response.

[mdmackillop]

Hi Rodante,
A couple of points,
Once the initial catchup is done, future data will be transferred automatically, so you need some code for two separate operations.
Do you have the account sheets made up or do you require the code to create and name them? (easily enough done from a template sheet)
One the data in the register has been entered, is it going to change? It would be simpler (maybe better?) to transfer the values to the account sheets, rather than the formula, if the data is "fixed".
You are showing the summary of the data below the data. Unless this is a specific requirement, it would simplify things again to show this at the top (easier to find as well).
Have you given any thought to securing the data against accidental change or deletion?
Regards
MD

[rodante]

Hi Rodante,
A couple of points,
Once the initial catchup is done, future data will be transferred automatically, so you need some code for two separate operations. I do not know coding.

Do you have the account sheets made up or do you require the code to create and name them? (easily enough done from a template sheet) I make them manually.

One the data in the register has been entered, is it going to change? No, once I posted data, this is maintained to be transferred to account sheets.

It would be simpler (maybe better?) to transfer the values to the account sheets, rather than the formula, if the data is "fixed". Is what I want, transfer the whole value data that is posted in the Register Sheet to corresponding Account Sheet.

You are showing the summary of the data below the data. Unless this is a specific requirement, it would simplify things again to show this at the top (easier to find as well).
Have you given any thought to securing the data against accidental change or deletion? No, any way to do this?
Regards
MD

I am very grateful for your responses. I do not know coding, only half Excel skills, I hope you can help to do this complicated, to me, this job. Thanks?

[mdmackillop]

Hi Rodante,
A little bit rough, but have a look at the following. Let me know how you get on.
Regards MD

[rodante]

Hi Rodante,
A little bit rough, but have a look at the following. Let me know how you get on.
Regards MD

Tanks mdmackillop, works perfect. I am very please for your help. Persons like you will be bless forever.

I have another question about your code:
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/10/2005 by MD MacKillop
'
'
Range("A1:G6").Select
End Sub
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 24/10/2005 by MD MacKillop
'
'
Range("A1:G6").Select
Range("G6").Activate
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect
End Sub

As I said before I do not know any coding, but I noticed that Range covers A1:G6, how can I changed this range to infinite? As data will be posted in Sheet Register constantly and for the wole year occupiyng the wole sheet I will need to change the Range.

Really appreciate your help, I hope you will continue to help me.
God bless you.

[mdmackillop]

Hi Rodante,
HTH,
The macros you mention are redundant and not used in the code. I've reposted my sample above to correct an error in the protection routine. Let me know how you get on. I'm sure there will be some teething problems.
MD

[rodante]

Hi Rodante,
HTH,
The macros you mention are redundant and not used in the code. I've reposted my sample above to correct an error in the protection routine. Let me know how you get on. I'm sure there will be some teething problems.
MD

Hi mdmackillop:

You are right, every time that I write something over the sheet sent a message that sheet is protected, I unprotected and got insert a new row. Also the code is limited to 5 sheets and every time that I press the button Initial Setup is transferring the data to open sheet accounts again. Could be possible to leave everything unprotected and easily changeable to insert another sheet in the book as a whole?

Thanks again for your time and effort.

[mdmackillop]

The initial setup should be used only once, then delete the button. If you add a new account number in column E, a sheet will be added when an amount is entered in columns F or G.

[rodante]

The initial setup should be used only once, then delete the button. If you add a new account number in column E, a sheet will be added when an amount is entered in columns F or G.

As I said before, I do not know any coding. Who do not kno it is same as not seeing. But thanks to you I am starting seeing. I will try from here to using your work, I hope I will be able to construct my assignment.

Thanks again and god bless you.