Autofill destination problem

Hello!

In one sheet I have data which is connected with formula with data in another sheet. I want every day with macro to autofill the data in the first sheet, but I don't know how to set a destination.

I tried this :

Private Sub CommandButton1_Click()

Range("A1").Activate

Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Activate
Loop

Set a = ActiveCell
Set b = ActiveCell.Offset(-1, 0)
Set c = ActiveCell.Offset(-1, 1)
Set d = ActiveCell.Offset(0, 1)

b.Select
Selection.AutoFill Destination:=Range("b:a"), Type:=xlFillDefault
c.Select
Selection.AutoFill Destination:=Range("c:d"), Type:=xlFillDefault

End Sub

but it is not working. How can I change this code so it works?