I am making a macro in excel
In the hoja1
excel book I have the form with which I capture the information, and in hoja2
that the read information must be saved
Sheet1 (Capture Form):
Sheet2 (Information Captured):
The data is read correctly but the problem comes when passing it to the hoja2
, it does not insert the new capture in the next row, but what it does is that the new capture replaces it in row 5.
In the visual basic of excel add a module and inside it code my macros
Macro Grabar_Datos()
:
Sub Grabar_Datos()
Sheets("Hoja1").Select
Dim producto As String
producto = Range("G7").Value
Dim precio As String
precio = Range("G9").Value
Dim cantidad As String
cantidad = Range("G11").Value
Dim total As String
total = Range("G13").Value
Dim fecha As String
fecha = Range("G15").Value
Sheets("Hoja2").Select
Range("B1").Select
Selection.End(xlDown).Select
Dim fila As Integer
fila = ActiveCell.Row
fila = fila + 1
Cells(fila, 2).Select
ActiveCell = producto
Cells(fila, 3).Select
ActiveCell = precio
Cells(fila, 4).Select
ActiveCell = cantidad
Cells(fila, 5).Select
ActiveCell = total
Cells(fila, 6).Select
ActiveCell = fecha
End Sub
Macro Limpiar_Datos()
:
Sheets("Hoja1").Select
Range("G7:G13").ClearContents
It is supposed or I understand that this line Selection.End(xlDown).Select
selects or activates the last cell that has a value but in my case it is not doing it like that, what it does is that it always selects or marks cell B4 as active
Why does that cell B4 always select me , how can I solve this?
Reviewing an example I have, I see that I have to add text or a value before the header row in my case B3 and I have to position in that cell B3
Sheets("Hoja2").Select
Range("B3").Select
Selection.End(xlDown).Select
Dim fila As Integer
fila = ActiveCell.Row
fila = fila + 1
This way if it works correctly but is there any way to solve this??
That I am not forced to position and add text in the previous cell of the header row which in my case is B3 .
Although I would not have used variables for all this since you only do it once, here is what the code would look like correcting various details:
To find out what the statement actually does , go
Selection.End(xlDown).Select
to the Excel sheet, activate cell B1, press the End key and the down arrow key, and you will notice the error.If instead of activating B1, you activate B4 you won't have any problems.
You can also do it the other way, by positioning the macro in a far cell, I imagine impossible to reach, down and telling it to find the first one with data up.
With
fila = Range("B65000").End(xlup).Row
you get the row with the last data.So it would be like this: