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?

 

[mikerickson]

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?

On sheet RESUMO, in column A, you could put the formula
=DATAVENDA, perhaps array entered on a range of cells.
and immediately below that, the formula =SUBSORVETES
and then =SUBMASSAS, etc.

When you would have presed the button, copy the formulas paste them (as formulas) immediatly below themselves and then copy/paste values the original range.

 

[RoryA]

Technically it is possible without VBA per se, but you would need some sort of macro (XLM or perhaps office script).

 

[Alex Piotto]

Thanks no problem