Copy across when a certain cell contains text.

[jgmckee]

Hi All,

First can I just start by saying thank you to all those that have helped me with my previous queries, I really can't thank you enough.

I have another query I'm hoping someone could help me with.

I have created an accounts ledger and this has the balances at the bottom of each page.

What I am trying to do is when text is entered into a specific cell, this will be the catalyst for the worksheet to copy the balances from the bottom of one sheet to the bottom of the next sheet and insert these into certain cells.

I'm sure their must be a simple way of doing this but for the life of me I just can't seem to get there.

Thanks in advance.

Jason.

 

[HalfAce]

Hello Jason,
You didn't provide much in the way of specifics (ie, sheet names, which cell you want to use to execute the code with, which cells to copy or which cells to paste to...) so, this is about as generic an example as I can think of at the moment.

With this code if you make a text entry in cell B2 it will copy cells A50:D50 and paste them in cells A51:D51 of the next sheet. (It will error out if you try to run it from the last sheet in the workbook. We can trap for this error and make it cycle back to the first sheet if you think it'll be necessary.)

The code goes into the sheet module for the sheet(s) you want it to work in.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B2]) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub

If Not IsNumeric(Target.Value) Then _
    [A50:D50].Copy ActiveSheet.Next.[A51]
End Sub

If this is too generalized for your needs just post back with more details and we can tailor it to suit.

Hope it helps.

 

[RAM]

Hi,

Half Ace is right. That would be how you "copy the balances from the bottom of one sheet to the bottom of the next sheet and insert these into certain cells. "

But if you want a really simple solution that displays, not inserts, the sum if text has been entered (in F4) I guess this would work:

Book6
	A	B	C	D	E	F	G
1		$ 25.00	$ 25.00	$ 25.00
2		$ 12.00	$ 24.00	$ 85.00
3		$ 36.00	$ 36.00	$ 36.00
4		$ 45.00	$ 21.00	$ 47.00		Any text
5		$ 54.00	$ 6.00	$ 47.00
6		$ 63.00	$ (9.00)	$ 47.00
7		$ 72.00	$ (24.00)	$ 47.00
8	Sum	$ 307.00	$ 79.00	$ 334.00
9
Sheet1

Book6
	B	C	D	E
7
8	$ 307.00	$ 79.00	$ 334.00
9
Sheet2

Edit: The cells in Sheet2 will remain blank until text has been entered.

Edit 2: If you're looking for an exact text then replace the formula in Sheet2 with

Book6
	B	C	D	E
7
8	$ 307.00	$ 79.00	$ 334.00
9
Sheet2

HTH RAM