Problem with 'FillAcrossSheets in Excel 2013 VBA

[ElephantEars]

I am trying to run the following routines in vba for Excel 2013
I want to insert this equation across all my sheets


With ThisWorkbook.Worksheets
.Item(1).Range("C29").Formula = "=NGL(+$A$29,+$B$1,+$B$6,+$B$4)"
.FillAcrossSheets .Item(1).Range("C29"), xlFillWithAll
End With

With ThisWorkbook.Worksheets
.Item(2).Range("E29").Formula = "=NGL(+$A$29,+$B$1,+$B$6,+$B$5)"
.FillAcrossSheets .Item(2).Range("E29"), xlFillWithAll
End With



I get a Method 'FillAcrossSheets' of object 'Sheets' failed

Below is the variables that have been declared. They are used by other parts of the code, but I think that may be my problem.



Dim strF As String, strP As String
Dim wb As Workbook
Dim ws As Worksheet


I would greatly appreciate any help someone has to offer!!!!!

Below is all of my code:

Sub Fix_NEW_LONG_FIN_STMTS_ADMIN_FEES()

'********Be sure to move Long-RRBC.xls file
'to another folder before running this macro
'then move it back after running it********!!!!!!!!!.
'Fix RRBC Manually!!!!!

    Dim strF As String, strP As String
    Dim wb As Workbook
    Dim ws As Worksheet


'Edit this declaration to your folder name
strP = "H:\Landvest\Excel Data\Financial Stmt Accr\Long Stmt" 'change for the path of your folder


strF = Dir(strP & "\*.xls") 'Change as required


Do While strF <> vbNullString


    Set wb = Workbooks.Open(strP & "\" & strF)
    Set ws = wb.Sheets(1) 'uses first sheet or if all the same names then ws.Sheets("yoursheet")


    Application.ScreenUpdating = False
    

    Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", _
        "Dec")).Select
    
    
    Rows("29:29").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A29").Select
    ActiveCell.FormulaR1C1 = "4051"
    Range("B29").Select
    ActiveCell.FormulaR1C1 = "      Admin Fees"
       
 
       
       
       
'    With ThisWorkbook.Worksheets
'    .Item(1).Range("C29").Formula = "=NGL(+$A$29,+$B$1,+$B$6,+$B$4)"
'    .FillAcrossSheets .Item(1).Range("C29"), xlFillWithAll
'    End With
       
'    With ThisWorkbook.Worksheets
'    .Item(2).Range("E29").Formula = "=NGL(+$A$29,+$B$1,+$B$6,+$B$5)"
'    .FillAcrossSheets .Item(2).Range("E29"), xlFillWithAll
'    End With
       
    
       
'    Range("C29").Formula = "=NGL(+$A$29,+$B$1,+$B$6,+$B$4)"
    
'    Range("E29").Formula = "=NGL(+$A$29,+$B$1,+$B$6,+$B$5)"
    
    
    Range("G28").Select
    Selection.Copy
    Range("G29").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    Range("I28").Select
    Selection.Copy
    Range("I29").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    Range("L28").Select
    Selection.Copy
    Range("L29").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    Range("N28").Select
    Selection.Copy
    Range("N29").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    Range("P28").Select
    Selection.Copy
    Range("P29").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    Range("R28").Select
    Selection.Copy
    Range("R29").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    Application.CutCopyMode = False
        
           
    Sheets("Sep").Select
    Application.ScreenUpdating = True
        
    Range("C212").Select
    Calculate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    strF = Dir()
  Loop
    
End Sub

 

[Truiz]

Try

[COLOR=#000000][FONT=Menlo]x = [/FONT][/COLOR][COLOR=#0000FF][FONT=Menlo]Array[/FONT][/COLOR][COLOR=#000000][FONT=Menlo]([/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"Sheet1"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo], [/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"Sheet2"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo], [/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"Sheet3"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo]) 
Sheets(x).FillAcrossSheets Worksheets([/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"Sheet1"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo]).Range([/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"C29"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo])[/FONT][/COLOR]

Make sure the array contains the name of the sheets you want to fill across and be sure about what you want to input.

or try something like

[COLOR=#333333]With ThisWorkbook.Worksheets[/COLOR]
[COLOR=#333333].Item(1).Range("C29").Formula = "=NGL(+$A$29,+$B$1,+$B$6,+$B$4)"[/COLOR]
[COLOR=#333333].FillAcrossSheets [/COLOR][COLOR=#333333]ThisWorkbook.Worksheets[/COLOR][COLOR=#333333].Item(1).Range("C29"), xlFillWithAll[/COLOR]
[COLOR=#333333]End With[/COLOR]