Macro/VBA issues, paste and next row

Copper Contributor

I am a newbie with macros and VBA.

 

I want to set up an excel sheet to import data from a website once a day, calculate the average of once specific row and transfer that value from the sheet to another sheet together with the current date. The next time the macro is run, I want the average and date to not be overwritten but the macro to continute in the next row.

 

I started the whole thing as a recorded macro but have had to modify ita bit, and the code currently looks like this:

 

Sub UtrR()
'5
' UtrR Makro
'

'
    Range("AA110").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-25]:RC[-2])"
    Sheets("Sammanställning").Select
      lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If lastrow > 1 Then lastrow = lastrow + 1
Range("A" & lastrow).Select
    ActiveCell.Value = Date
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
If lastrow > 1 Then lastrow = lastrow + 1
Range("B" & lastrow).Select
    ActiveCell.Value = "=Blad1!AA110"

End Sub

 

So my issues:

 

1. Since I link AA110 to the target sheet, the value changes every time I import new data to the sheet. How do I get it to copy only the static value and not link to AA110? I've tried some variants of PasteSpecial without success. 

2. I am not getting the lastrow solution to work. I want the next time the macro is run the data is being written into the next empty row in the columns A and B. How do I get it to work? 

 

Grateful for all help!

1 Reply

@sawase 

Try this version:

 

Sub UtrR()
    Dim lastrow As Long
    Worksheets("Blad1").Range("AA110").FormulaR1C1 = "=AVERAGE(RC[-25]:RC[-2])"
    With Worksheets("Sammanställning")
        lastrow = .Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lastrow > 1 Then lastrow = lastrow + 1
        .Range("A" & lastrow).Value = Date
        .Range("B" & lastrow).Value = Worksheets("Blad1").Range("AA110").Value
    End With
End Sub