Is it possible without VBA?

[Alex Piotto]

Hi.
Just out of curiosity, but also good for learning...

I have two sheets, VENDAS and RESUMO.
Inside VENDAS I have a few NAMED cells, with some text and numbers inside.
Values of NAMED cells will change overtime, so I made a button and a script.

With this script I send the NAMED cells values into RESUMO sheet, one row at a time, every time I click the Button.

Private Sub CommandButton1_Click()

        lastRow = Sheets("RESUMO").Cells(Rows.Count, "A").End(xlUp).Row
       
        ThisWorkbook.Sheets("VENDAS").Range("DATAVENDA").Copy
        ThisWorkbook.Sheets("RESUMO").Range("A" & lastRow + 1).PasteSpecial Paste:=xlPasteValues
       
        ThisWorkbook.Sheets("VENDAS").Range("SUBSORVETES").Copy
        ThisWorkbook.Sheets("RESUMO").Range("B" & lastRow + 1).PasteSpecial Paste:=xlPasteValues
        
        ThisWorkbook.Sheets("VENDAS").Range("SUBMASSAS").Copy
        ThisWorkbook.Sheets("RESUMO").Range("C" & lastRow + 1).PasteSpecial Paste:=xlPasteValues
       
        ThisWorkbook.Sheets("VENDAS").Range("SUBPADARIA").Copy
        ThisWorkbook.Sheets("RESUMO").Range("D" & lastRow + 1).PasteSpecial Paste:=xlPasteValues
       
        ThisWorkbook.Sheets("VENDAS").Range("SUBPASTELARIA").Copy
        ThisWorkbook.Sheets("RESUMO").Range("E" & lastRow + 1).PasteSpecial Paste:=xlPasteValues
       
        ThisWorkbook.Sheets("VENDAS").Range("TOTAL").Copy
        ThisWorkbook.Sheets("RESUMO").Range("F" & lastRow + 1).PasteSpecial Paste:=xlPasteValues
       
        Application.CutCopyMode = False

Sheets("RESUMO").Activate
Sheets("RESUMO").Range("B1").Select
           
End Sub

Everything works fine.

What I am asking is: It is possible to achieve the same without VBA?

 

[rondeondo]

Hi
Do you want to have the cells in the RESUMO sheet get the values only at the pressing of the button or are you happy for it to be instant?
if you have a named cell called DATAVENDA you can just have a formula of

=DATAVENDA

if you want to have the values be triggered then the VBA may be the only way. Unless you want to turn off auto calculate, then you can hit F9 to send the values.
Or you could put in a button to do that, but that would be overkill

 

[Alex Piotto]

Hi rondeondo thank for reply.

I do not actually "need" a button... but the button allows me to send all the data at once on the other sheet.

Let me explain better... hopefully.

The sheet VENDAS contains a list of product and some calculation of subtotals and totals and taxes.
Nothing especial.

Everyday in the sheet VENDAS i will write the quantities of the items my client asked, and i need to send them to the RESUMO sheet to create a list.
So at the end of the month i will have all the sales listed
Like:

date of before yesterday - bread cost - pasta cost - cakes cost - ice cream cost - subtotal of the day
date of yesterday - bread cost - pasta cost - cakes cost - ice cream cost - subtotal of the day
date of today - bread cost - pasta cost - cakes cost - ice cream cost - subtotal of the day
etc.

The script put the data i need in the next empty row in RESUMO.
How to do it without vba?
It is even possible?

You know... all because the guy that uses the workbook forget to enable macros all the time.
Even if i made the first sheet that state: PLEASE ENABLE MACRO BEFORE PROCEED!!!

 

[RoryA]

Please take a minute to reread the forum rules regarding cross-posting and then add the appropriate links (as you did on the other forum). Thanks.

 

[Alex Piotto]

Guilty as charged... sorry about it. I cross posted here

It is possible without VBA? [SOLVED]

I already posted in MrExcel with no avail... sorry for the cross posting, Hi. Has been a while... Just out of curiosity, but also good for learning... I have two sheets, VENDAS and RESUMO. Inside VENDAS I have a few NAMED cells, with some text and numbers inside. Values of NAMED...

www.excelforum.com

 

[MARK858]

And the other thread on the other board?

 

[Alex Piotto]

Well... they said it cannot be done without vba.
I believe them!
I already got my vba working and i am happy anyway!

 

[MARK858]

Well... they said it cannot be done without vba.

I don't see them saying that?

 

[Alex Piotto]

I don't see them saying that?

Copy values from one sheet to another and organize them by rows without VBA

(I already posted in MrExcel with no avail... https://www.mrexcel.com/board/threads/is-it-possible-without-vba.1145797/ Hi. Just out of curiosity, but also good for learning... I have two sheets, VENDAS and RESUMO. Inside VENDAS I have a few NAMED cells, with some text...

www.excelforum.com

 

[MARK858]

My fault, didn't refresh ? as long as you are happy all good.