[SOLVED] Running vba on different Worksheet with "with statement"

Running vba on different Worksheet with "with statement"

I am trying to run a macro on a specific sheet and start it on a different sheet.

I understand I could just select the sheet in Question for every line of Code but I would like to know why it is not working when using the "with Statement" (see Code below).

Sub IncludeNew()

Application.ScreenUpdating = False

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = Sheets("Occupancy")

With ws

    .Columns("C:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Columns("F:F").AutoFill Destination:=Columns("C:F"), Type:=xlFillDefault
    .Range("Quaterly").Columns(2).copy
    .Range("Quaterly").Columns(2).EntireColumn.Insert
    .Range("C6").copy
    .Range("Quaterly").Cells(12, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Application.ScreenUpdating = True
Application.CutCopyMode = False

End With

End Sub

The error message I get is "Run-time error 1004" AutoFill method of range class failed. When running it on the the actual sheet it works just fine!

the error is at .Columns("F:F").AutoFill Destination:=Columns("C:F"), Type:=xlFillDefault

Solution

You are nearly there. You missing the .. Try .... Destination:=.Columns("C:F")..... instead.

Also, what's the point declaring a workbook variable and not using it? When declaring your worksheet variable you could use wb.Sheets("Occupancy")