Put =today if visible

Hello all, i need help on 1 issue.

I have a column that has subtotal(109,a1:a1) to check if visible.

Then i need to create a macro that if subtotal = 1 (row visible) then puts in another column value today. But the data base is constantly increasing which means that the macro needs to do probably a while till subtotal different then empty.

How can i do this?

Please let me Know if u need more info

Originally Posted by xladept

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Check file in Attach, i need a macro for that, but there will columns inserted everytime (only cell A1 will have ALWAYS value, that's why subtotal is in it)
and autofilters will be applied, just have to check while column D has value 1, put in same row at column b the today value.

Get back to me asap

Although this value has to become as value i don't want that the cell to be =Today, cuz it need to be a fixed day and not change once i open the value.

The solution is to select and copy cells in the same position only for value.

Please help me out on this.

Originally Posted by xladept

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Any idea to solve my problem i would be very thankful.

Thank you

My problem is Simple to understand.

I have 2 columns, 1 with number "1" and "0" . If the number im column 1 is equal to "1" then insert in column 2 (same row) the formule "=today()"

Then since i just want the value of today and not formule, i need to copy and paste values of visible cells.

Can someone help?

For your example workbook, I think you just want:

Please Login or Register  to view this content.

This may actually be more than you need however as it assumes the values can change. Note that it looks to the underlying value, not the formula. Formula changes do not trigger the worksheet change event

Ah yes, missed that that was a date - the funky format threw me off:

Please Login or Register  to view this content.

That's right alan, but a formula change doesn't trigger the change event

Hello all,

Tomorrow i will so both sheets so you can better understand this simple task i want. Although the 1 that shows in the column is the result of subtotal formule to determine visible cells....

I will probably opt for a while column cellvalue is =1 then offset(-2,0) and cellvalue=today after all that copy ALL visible cells and paste as values

Some help with that solution?

So you just want to loop through an put the date in the relevant column if there's a 1 in the cell? You do realise that the subtotal isn't doing anything right?

I'll have alook tomorrow for you

Yes do a loop on one column( which is only 1 IF Subtotal is applied) and if found put value of today date in other column (same row) but after that i need to copy visible cells on date column and paste as value otherwise once i open workbook it puts today but i dont want that, i want a fixed date.

So.. To conclude, i agree with you except that the subtotal is needed.

Thanks in advanced.

P. S. The objective of this is to put the date of order emission to production.

I think you want:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select KylesSuperDuperSub in the list
3. Click the Run button

That code doesn't work for what i need.

I pasted a new attach that will show you better what's the need.
I need to check if column D has the value 1 or not. If yes put TODAY date.
Otherwise do nothing, let the the cell as is.

After inserting the date, then it should be as value, and not =today formule, since i don't want that the cell value each day has a different value. Witch means i need to select.copy then paste as value.

Hope someone can help me.

Originally Posted by Kyle123

I think you want:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select KylesSuperDuperSub in the list
3. Click the Run button

What exactly doesn't that code do? It doesn't use formulas and puts today's date in the relevant cell. If you can tell me what it doesn't do I can help

Originally Posted by Kyle123

What exactly doesn't that code do? It doesn't use formulas and puts today's date in the relevant cell. If you can tell me what it doesn't do I can help

It has to be able to put in everycell today date. I tried the code that you can see in file attach (last post) and didnt post date in all (1s) 2 columns to left.

Have you tried and downloaded my last post?

I didnt understand nothing you said. But your images are perfect. Thata what i want. I will test both codes again and will update my post comment. Thanks

Originally Posted by Doc.AElstein

Hey Brain___

. have you seen and read have Posts #18 and Post #19

I dont understand what you're writting. I am writting in a portuguese phone with swipe digitalization and some words go with errora like (thatA > that's)

Thanks for your help. Sooner i have my computer with me i will test both codes and check if in activecell =1 the result in offset(0,-2) is today date in value. On all range till the last value..

Already solved my problem with a little twist on your codes.

Thanks for all your help.

Ended like this:

Please Login or Register  to view this content.

Already solved my problem with a little twist on your codes.

Thanks for all your help.

Ended like this:

Please Login or Register  to view this content.

I tried doing a loop but loop doesn't work when i filter columns.. so solution is as told.

Thank you all