Macro to autofill to the last column with data in Excel?

Question

I have a spreadsheet and have a macro that resets the data each month by moving some around and clearing some out which also deletes my autosums in those certain columns. I need to put these sums back in and I believe the autofill feature is the best way to do this. However, I have a lot of different worksheets and the autosum is never on the same row. The below is the macro I have so far. The line with arrows next to it is where it debugs. The macro just above that gets me to the correct cell that I need to autofill from I'm just not sure how to make that autofill macro to not be for a certain row and to be relative. Any help is much appreciated.

  Sub Reset_Each_Spare()
      Range("E16:F5000").Select
      Selection.Copy
      Range("P16").Select
      Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Range("I16:J5000").Select
      Selection.Copy
      Range("R16").Select
      Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Range("L16:M5000").Select
      Selection.Copy
      Range("T16").Select
      Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
      Range("D16:D5000").Select
      Selection.ClearContents
      Range("H16:H5000").Select
      Selection.ClearContents
      Range("K16:K5000").Select
      Selection.ClearContents
      Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
      ActiveCell.Offset(0, 1).Select
      >>>Selection.AutoFill Destination:=Range("C:U"), Type:=xlFillDefault
      Range("A1").Select

End Sub

Jamie Walker Jamie Walker · Accepted Answer · 2014-09-10T13:28:52

I answered my own question guys, thanks for anybody who took the time to look at my question. I just replaced the line above marked by >>> with the following line of code.

  Selection.AutoFill Destination:=ActiveCell.Range("A1:S1"), Type:=xlFillDefault

Macro to autofill to the last column with data in Excel?

1 Answers