Solved: Transfer dynamic cell value to second sheet

[Amanda1]

OK,

I've looked in the KB and didn't find anything to help me. This will be so simple for all you masters, you won't even feel it is worth your effort to anser - but pls do so -cause I'm going grey.

Sheet 1 Cell B12 needs to be automatically populated from Sheet 2 B"whatever". It is a total and the cell row will change daily as users add and delete rows on sheet 2 - the column will always be static at "B" & the adjacent A cell is always populated with "Total". (Sheet A is all static)

I've got lots of these to fill in, but if you can give me a hint how to do the first one, I can go on from there.

I've even tried doing Index/Match etc but I have a feeling that it doesn't work across sheets.

Pls help.

Thanks
Amanda (I like these smilies)

[johnske]

Is this what you mean?[VBA]Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.Range("B12") = Range("B" & Rows.Count).End(xlUp)
End Sub[/VBA] This is a 'worksheet event' and the code goes into the code module for Sheet2

[Amanda1]

Hi John

Yes and No. I've copied the code - now you said it is a worksheet code, (whatever that is), so I opened VB & copied it into the relevant sheet as opposed to in a "module" - is that right??? (Just to clarify, I've put it in the sheet that has the total in - not the sheet I need it transferred to).

Now, I've changed the sheet1 to the name of the sheet that I need the total to be put into - but it gives me an error - tried various ways of doing it without any luck.

What am I doing wrong? The sheet I need it to go into is named "Coverpage" - very original.

Oops, I didn't get the zip file, did you get chance to send it?

Thanks for your help (that's me)

[johnske]

The sheet modules are in the VBE window> Go in there and click View > Project Explorer. In the Project Explorer you'll see a number of sheets listed, double-click the one you want the code to go into and the code-pane for that sheet will appear - paste the code in there.

Sheet1 is the 'safe way to do it, you can use Worksheets("Coverpage") if you like, but if you ever change the name of the sheet you'll then have problems running your code.

Yes, I sent it many hours ago, maybe I got the addy wrong. Click my 'name' and send me an email, I'll send it again as a reply

[Amanda1]

Hi John,

It works, ( I had put it in the right place, but I had two workbooks open & got a little confused in the process).

It is typical South African summer thunderstorms at the moment & one is about to descend on me - rather than risk all our PCs I tend to shut them down, (lost LAN cards etc once before through lightning & yes I have got almost every form of protection you can think of - but when these storms hit, they hit).

Anyway as soon as the storm is over - I'll copy the script over to all the other pages & let you know. Can I wait till then to put a solved note on.

Thanks so much for your help. (read your profile - it sounds interesting).

[malik641]

Thanks for your help (that's me)

I just wanted to say this is HILARIOUS I like your personality Amanda, I feel that way too when I get stuck on something for a while.

[Amanda1]

My problem though is that I get stuck on things for a long time - not just for a while.

But having said that, I'm feeling quite chuffed with myself, Matt, John & Malcom helped me and from there I have learnt a huge amount in the last few days. I have now managed to get a workbook up and running with various scripts in that match and tranfer various fields of data all over the place - I tried it out last night with loads of entries in - all my fingers were crossed cause I'd changed the original scripts to modify them where I needed to & I was petrified that they would all bomb out on me - but all 48 scripts ran correctly.

Now don't burst my bubble and tell me that it would have taken all you guys about one hour to do it - I know that - but just over a week ago - I wouldn't have had an idea where to start - so I'm quite chuffed.

But I see that no-one has managed to tell me yet - how I can hide the password in a script - John doesn't think it can be done, (neither do I but hope springs eternal).

Thanks for the compliment & take care

Amanda