Autofil until the end of the column

Question

My original program is able to find the Cells I need based on Month and Week now I need to modify the program to copy the last used cell in Row 5 and paste it until the end of the column.

Ex. If the month if November and it is the 4th week then the program knows to go there and fill in the information. I cannot figure out how to paste value from Nov wk 4 into the rest of the column. Also my range could be H5:BA5 or BC5:DB5 depending on where the month and week start.

I have added a picture that shows how my data is set up, the highlighted cells need to be filled in until the end

With ThisWorkbook.Sheets(SheetName)
  Dim c2 As Integer
  Dim LastCol2 As Integer
  c2 = 2
  LastCol2 = .Cells(4, .Columns.Count).End(xlToLeft).Column
  Do Until .Cells(1, c2).Value = "Sept" And .Cells(4, c2).Value = "Wk 5"
    If .Cells(1, c).Value = MonthSel And .Cells(4, c).Value = WkSel Then
      .Cells(5, c2).Select
      Selection.copy
      ActiveCell.Offset(0, 1).Select
      Selection.Paste
      Application.CutCopyMode = False
      Selection.AutoFill Destination:=Range("H5:BA5"), Type:=xlFillDefault
      Range("H5:BA5").Select // need to change this range to reach the end of column 5
    End If
  Loop
End With

This line `Selection.AutoFill Destination:=Range("H5:BA5"), Type:=xlFillDefault` needs to change to reflect the last row you want filled. For example: `Selection.AutoFill Destination:=Range("H5:BA55"), Type:=xlFillDefault`. — Brian, Nov 29 '16 at 16:29
While my solution cleans up your code, I don't think it really does what you want. I'd suggest a simple code of autofill across from column B — Chrismas007, Nov 29 '16 at 16:48

score 3 · Answer 1 · edited May 23 '17 at 11:45

First off you need to STOP USING .SELECT!

Secondly, you already figured out how to find the last used column in your code. Why not use the same logic to find the last used row?

With ThisWorkbook.Sheets(SheetName)
    Dim c2 As Long, LastCol2 As Long, LastRow As Long
    c2 = 2
    LastCol2 = .Cells(4, .Columns.Count).End(xlToLeft).Column

    Do Until .Cells(1, c2).Value = "Sept" And .Cells(4, c2).Value = "Wk 5"
        If .Cells(1, c).Value = MonthSel And .Cells(4, c).Value = WkSel Then
           .Cells(5, c2).copy
           .Cells(5, c2).Offset(0, 1).Paste
           Application.CutCopyMode = False
           LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
           .Cells(5, c2).Offset(0, 1).AutoFill Destination:=Range("H5:BA" & LastRow), Type:=xlFillDefault
        End If
    Loop
End With

+1 Good solution! You might consider eliminating 3 lines with `.Cells(5, c2).copy .Cells(5, c2).Offset(0, 1)`. — Brian, Nov 29 '16 at 16:41
I'm re-reading your requirements and I think i have a better solution. I don't think either your code nor my code is even close... — Chrismas007, Nov 29 '16 at 16:43

Chrismas007 · Accepted Answer · 2016-11-29T17:41:20.660

0

A much simpler solution (if we assume you have some sort of lookup formulas in your cells) is to grab all of column B and auto-fill all 52 weeks:

Sub TestingIt()
    Dim LastRow As Long
    With ThisWorkbook.Sheets(SheetName)
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row 
        Range("B5:B" & LastRow).AutoFill Destination:=Range("B5:BA" & LastRow), Type:=xlFillCopy
    End With
End Sub

edited Nov 29 '16 at 17:41

answered Nov 29 '16 at 16:51

Chrismas007

6,085
4
24
47

Why did you use LastRow and not LastColumn? – tamara smith Nov 29 '16 at 16:57
But I know all of the values need to be pasted into the same row which is row 5 but different columns – tamara smith Nov 29 '16 at 17:03
@tamarasmith That is what auto-fill does. It keeps the formulas from each row and copies them across to all weeks. – Chrismas007 Nov 29 '16 at 17:05
I am not sure your code does what I need. I need it to copy the last filled cell in row 5 and then paste that value across the entire row until September wk5. – tamara smith Nov 29 '16 at 17:07
I also receive an error that says AutoFill method of Range class failed – tamara smith Nov 29 '16 at 17:13
@tamarasmith I just tested some sample data and it worked for me. Perhaps your `SheetName` variable is wrong? – Chrismas007 Nov 29 '16 at 17:16
You were correct. I got it to autofill but the numbers keep increasing by 1 in each cell. How do I just paste the value from the last cell I used, it doesn't have to be B5, it could be H5 – tamara smith Nov 29 '16 at 17:23
@tamarasmith Try using `xlFillCopy` for the type parameter. – Brian Nov 29 '16 at 17:32

Autofil until the end of the column

2 Answers2