copy & paste value to the end of datatable for each worksheet in workbook

Question

I have different sheets in a Workbook. In each sheet in "C1" is a value I want to have applied to all rows starting from "K8" until the last row of data. I want to do this for each sheet.

I've managed to copy and paste the value per sheet. But filling the value to the end of the data is the tricky part for me.

The Selection.AutoFill only works for the active sheet. It does the job correct, but all the other sheets, only have "K8" filled.

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
        If (sh.Name <> "bla") And (sh.Name <> "blub") Then
            With sh
            sh.Range("C1").Copy
            sh.Range("K8").PasteSpecial Paste:=xlPasteValues
            Selection.AutoFill Destination:=Range(Selection, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))
            End With
        End If
    Next sh

I'd like to have the Autofill part work for all the sheets in the workbook. Can anyone help?

Don't use `Selection`. Instead specify the specific sheet `sh` and a range like `sh.Range("K8")`. Also see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Jan 16 '19 at 12:34

score 1 · Accepted Answer · answered Jan 16 '19 at 13:00

It's not clear how you define the last row. I have used J as the reference column so change to suit. As PEH says, using Select(ion) is dicey as

Sub x()

Dim sh As Worksheet, n As Long

For Each sh In ActiveWorkbook.Worksheets
    If (sh.Name <> "bla") And (sh.Name <> "blub") Then
        With sh
            n = .Range("J" & Rows.Count).End(xlUp).Row
            .Range("K8:K" & n).Value = .Range("C1").Value
        End With
    End If
Next sh

End Sub

copy & paste value to the end of datatable for each worksheet in workbook

1 Answers1