RUNTIME ERROR 1004 : AUTOFILL METHOD OF RANGE CLASS FAILED

[thabisothebe]

Hi, I am running into a problem with the code below, it works fine when I step through the code but when I play it using F5 I receive a runtime error 1004 :

Sub Create_Report()
Dim tbTemp As ListObject
Dim RowsTb As Long, LastRow As Long
' Start = Timer
Call Entry_Point

'create an object variable for the table inside Region Temp tab
Set tbTemp = shRegion.ListObjects("TableTemp")
RowsTb = tbTemp.DataBodyRange.Rows.Count
'Remove existing data in regional table except for line 1 if there is more thann 1 line of data
If RowsTb > 1 Then tbTemp.DataBodyRange.Rows("2:" & RowsTb).Delete
LastRow = shAll.Range("A" & Rows.Count).End(xlUp).Row
myRegion = shStart.AxRegion.Value
myDate = Format(shAll.Range("C2").Value, "YYYYMM")

shAll.Range("I2").FormulaR1C1 = "=VLOOKUP(RC[-8],MCompany,4,FALSE)"
' LastRow = shAll.Range("A" & Rows.Count).End(xlUp).Row
shAll.Range("I2").AutoFill Destination:=Range("I2:I" & LastRow)
Application.Calculate

Monthly_Sales_Reporting_Template_Tool_Start.xlsm
	A	B	C	D	E	F	G	H
1	Company ID	Sales Document	Document Date	Customer code	Article code	Reject	Quantity	Sales Value
2	1030AT	84030	2018/10/07	8050	104	1	430	5160
3	1030AT	84030	2018/10/07	8050	105		120	1200
4	1030AT	84030	2018/10/07	8050	107	1	690	3450
5	1030AT	84031	2018/10/08	8040	104		870	10440
6	1030AT	84031	2018/10/08	8040	107		760	3800
7	1030AT	84031	2018/10/08	8040	110	2	500	30000
8	1030AT	88112	2018/10/11	8040	110		480	28800
9	1030AT	88112	2018/10/11	8040	119		630	39060
10	1030AT	88112	2018/10/11	8040	120		150	8700
11	1030AT	88112	2018/10/11	8040	116	4	760	4560
12	1030AT	88112	2018/10/11	8040	111		590	11800
13	1030AT	88112	2018/10/11	8040	110		530	31800
14	1030AT	88112	2018/10/11	8040	109		260	7280
Summary

 

[Alex Blakenburg]

There is too much information missing for us to troubleshoot this.

• When it errrors out what line of code is highlighted when you go into Debug mode ?
• What is this doing Call Entry_Point ?
• shAll & shStart are not declared and not set
• Does this "shStart.AxRegion" actually work, what is AxRegion ?
• What is the activesheet ? The autofill destination is relying on shAll being the Activesheet.

 

[Alex Blakenburg]

Does your "like" indicate you have resolved the issue ? In which case could you elaborate in case it helps others.
If not, do you want to reply to the questions in my previous post.

 

[thabisothebe]

• When it errrors out what line of code is highlighted when you go into Debug mode ?

shAll.Range("I2").AutoFill Destination:=Range("I2:I" & LastRow)

• What is this doing Call Entry_Point ?

Sub Entry_Point()
    With Application
        .StatusBar = "Your Assistant is Busy Working"
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    
    End With
End Sub

• shAll & shStart are not declared and not set

These are sheet codes

• Does this "shStart.AxRegion" actually work, what is AxRegion ?

Ax.Region is the drop-down selection that is the ListFillRange

• What is the activesheet ? The autofill destination is relying on shAll being the Activesheet

?ActiveSheet.name
Start
Perhaps this is the problem when I add a line to activate "shAll" sheet it gives me an error 1004 on myRegion?
debug line becomes
Correct the sheet name shAll ("Summary" sheet) is the active sheet

Please kindly assist.

 

[Alex Blakenburg]

Just trying the easy one first, replace the line it is erroring out on with this and see if we are lucky.

    With shAll
        .Range("I2").AutoFill Destination:=.Range("I2:I" & LastRow)
    End With

 

[thabisothebe]

Does your "like" indicate you have resolved the issue ? In which case could you elaborate in case it helps others.
If not, do you want to reply to the questions in my previous post.

• When it errrors out what line of code is highlighted when you go into Debug mode ?

[COLOR=rgb(65, 168, 95)]shAll.Range("I2").AutoFill Destination:=Range("I2:I" & LastRow)[/COLOR]

• What is this doing Call Entry_Point ?

• Just trying the easy one first, replace the line it is erroring out on with this and see if we are lucky.
  
  

      With shAll
          .Range("I2").AutoFill Destination:=.Range("I2:I" & LastRow)
      End With

Hi Alex thank you so much for your assistance?!!

I managed to solve the problem based on your observation "The autofill destination is relying on shAll being the Activesheet" . I added a line of code to activate the correct sheet before I executed the autofill method and it works just fine now:

[/B][/COLOR][COLOR=rgb(0, 0, 0)][B]shAll.Activate[/B]
    shAll.Range("I2").FormulaR1C1 = "=VLOOKUP(RC[-8],MCompany,4,FALSE)"
    shAll.Range("I2").AutoFill Destination:=Range("I2:I" & LastRow)
    Application.Calculate[/COLOR][COLOR=rgb(65, 168, 95)]

[/CODE][/CODE]

Sub Entry_Point()
    With Application
        .StatusBar = "Your Assistant is Busy Working"
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    
    End With
End Sub

• shAll & shStart are not declared and not set

These are sheet codes

• Does this "shStart.AxRegion" actually work, what is AxRegion ?

Ax.Region is the drop-down selection that is the ListFillRange

• What is the activesheet ? The autofill destination is relying on shAll being the Activesheet

?ActiveSheet.name
Start
Perhaps this is the problem when I add a line to activate "shAll" sheet it gives me an error 1004 on myRegion?
debug line becomes
Correct the sheet name shAll ("Summary" sheet) is the active sheet

Please kindly assist.

 

[Alex Blakenburg]

Can you clarify. Is it working now ?
If not is it erroring out and if so what is the error message and on what line ?
If the error is on the line myRegion = shStart.AxRegion.Value, then I need more information on what AxRegion actually is because that syntax does not make sense. Is it a Named Range and if so what is the defined range ?

 

[thabisothebe]

Can you clarify. Is it working now ?
If not is it erroring out and if so what is the error message and on what line ?
If the error is on the line myRegion = shStart.AxRegion.Value, then I need more information on what AxRegion actually is because that syntax does not make sense. Is it a Named Range and if so what is the defined range ?

Hi Alex

Yes its working thank you once again

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.