Copy data from one sheet to another without formulas automaticly

Should only need one line of code. This belongs in the Sheet1 object.

Please Login or Register  to view this content.

Hmm, thanks for your reply, don't get it working. Maybe because I have the two sheets renamed:

sheet1= Hotový jídelní lístek k tisku

sheet2= Lístek bez funkcí

So I put the code in sheet 1 and rename ("sheet2") as ("Lístek bez funkcí"), but nothing happens. When I let the name as ("sheet2") also nothing happens... Do you see what I'm doing wrong?

By nothing happens, do you mean you make a change to a formula on sheet1, and the corresponding value doesn't appear/change on sheet2?

Maybe here I do something wrong. I don't really understand how these macros are working. My sheet2 is completely empty. It's just a new empty sheet. I need all the data from sheet1 exactly the same on sheet2, only without the formulas in the cells. What is the first step? To copy all content from sheet1 to sheet2?

What I mean by "nothing happens" is that sheet2 stays a completely empty sheet, when I put the code to sheet1 and change something on sheet1 :-)

Please can anybody help me how to automaticly copy sheet1 to sheet2 without the formulas in the cells, only the text?

Here is a example of what I need: copy the text from all the cells from sheet1 to sheet2, so in sheet2 there is text in the cells and not the formulas like in sheet1. And automaticly like any time the text in sheet1 is changing, it will also change in sheet2. Anybody help me please?? It's unfortunatly in a hurry :-/

How to automatickly copy data sheet1 to sheet2 without formulas.xlsx

The code i originally provided works perfectly fine for me in the workbook you just uploaded. I'm reattaching it to that you can see it working.

I also included a "Sub bee88" that you can run if you really don't want to copy/paste the values over from sheet1 to initially set up sheet2.

1_How to automatickly copy data sheet1 to sheet2 without formulas.xlsm

Unfortunatly that also doesn't help - if I for place in any cell some "text", then the "text" is automaticly in sheet2 also, that works fine, but the changed data from a cell with formula stays as it was originally

I've had / read this discussion before in this forum, and as far as I know, there's no way to trigger a _Change event when a cell changes it's own value as the result of a formula. That is, _Change only triggers when a user changes a value.

If I'm wrong, I'd be interested to know the solution.

Yeah, I don't think there is going to be a solution to what you want to do, unless:

You move the _Change event to the Source sheet, then use an algorithm to transpose the Target Address to the corresponding address on Sheet2.

Like if the values on Sheet 2 are always two columns to the left and 14 rows down from where they are on the Source sheet, you could do:

Please Login or Register  to view this content.

If there's no logic to the positioning of the cells on Sheet2 and Source, then I don't have a solution for you.

No, there is no logic to that positioning...

Is there really no way round? I mean, if I do a "copy" and then "paste - values" to another sheet or workbook, then everything is allright. Doesn anybody know a way to do a copy - past to other sheet or workbook automaticly when there is changing something in the sheet (though only because of a formula in the cell)?

Ok, solved, I figured a way out, by just making a button and creating a macro by following Ctrl A - Ctrl C - Ctrl V - past values.

Why do you always loose info or it changes when moving from one excel sheet to the next?