Hi,

The following recorded macro will only work for specific cells. What I would like is to run dynamic code using a command button placed on the sheet.

The command button becomes ready to be pressed when a cell in colJ is selected

Based on the macro code below for example, next weeks manually selected cell would J35

Then insert one cell by shifting down

Then select the cell two cells up J33

Autofill J33:J36

From row 33 select N:P

Autofill N33:P36

Finish by selecting L37 (previously L36 was selected) 

Sub WeeklySales()
‘
‘ WeeklySales Macro

Range(“J34”).Select ‘ Manually selected cell in colJ 

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ‘ Then insert one cell by shifting all cells in colJ down one cell

Range(“J32”).Select ‘ After this select the cell two cells up from J34

Selection.AutoFill Destination:=Range(“J32:J35”), Type:=xlFillDefault ‘ Drag down the formula that is in colJ 

Range(“J32:J35”).Select ‘ This is the range to be dragged down, four cells in total, J32 plus three cells

Range(“N32:P32”).Select ‘ Now select the range N:P and match the same row 32 as used earlier

Selection.AutoFill Destination:=Range(“N32:P35”), Type:=xlFillDefault ‘ Drag down the formula that is in each of the cells in N:P

Range(“N32:P35”).Select ‘ The formulas are auto filled

Range(“L36”).Select ‘ Finish by leaving the sheet  activate the sheet 
End Sub

If you can provide some help with this task I would be very great full. 

​Hi, The following recorded macro will only work for specific cells. What I would like is to run dynamic code using a command button placed on the sheet. The command button becomes ready to be pressed when a cell in colJ is selected Based on the macro code below for example, next weeks manually selected cell would J35Then insert one cell by shifting downThen select the cell two cells up J33Autofill J33:J36From row 33 select N:PAutofill N33:P36Finish by selecting L37 (previously L36 was selected)   Sub WeeklySales()” WeeklySales Macro Range(“J34”).Select ‘ Manually selected cell in colJ Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ‘ Then insert one cell by shifting all cells in colJ down one cell Range(“J32”).Select ‘ After this select the cell two cells up from J34 Selection.AutoFill Destination:=Range(“J32:J35”), Type:=xlFillDefault ‘ Drag down the formula that is in colJ  Range(“J32:J35”).Select ‘ This is the range to be dragged down, four cells in total, J32 plus three cells Range(“N32:P32”).Select ‘ Now select the range N:P and match the same row 32 as used earlier Selection.AutoFill Destination:=Range(“N32:P35”), Type:=xlFillDefault ‘ Drag down the formula that is in each of the cells in N:P Range(“N32:P35”).Select ‘ The formulas are auto filled Range(“L36”).Select ‘ Finish by leaving the sheet  activate the sheet End Sub If you can provide some help with this task I would be very great full.    Read More

​