Run-time error '1004': Method 'Range' of object '_Global' failed.

Question

Hi,

I'm not experienced in VBA and my macro worked but now it responses with error: Run-time error '1004': Method 'Range' of object '_Global' failed.

it breaks in line: Range("Refund[[#Headers],[Column1]]").Select

Can You help me?

Sub Macro18()

'

' Macro18 Macro

' Table

'

' Keyboard Shortcut: Ctrl+Shift+A

'

Rows("1:1").Select 

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("A1").Select 

ActiveCell.FormulaR1C1 = "SPOT" 

Range("B1").Select 

ActiveCell.FormulaR1C1 = "1" 

Range("C1").Select 

ActiveCell.FormulaR1C1 = "2" 

Range("D1").Select 

ActiveCell.FormulaR1C1 = "3" 

Range("D2").Select 

Application.CutCopyMode = False 

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$13051"), , xlYes).Name \_ 

    = "Table1" 

Range("Table1[#All]").Select 

ActiveSheet.ListObjects("Table1").Name = "Refund" 

Columns("B:C").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("Refund[[#Headers],[Column1]]").Select 

Columns("D:D").EntireColumn.AutoFit 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "ZZZS številka obračuna" 

Range("Refund[[#Headers],[Column2]]").Select 

ActiveCell.FormulaR1C1 = "ZZZS številka zavarovanca:" 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Priimek in ime:" 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Številka ePotrdila:" 

Columns("F:F").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Šifra razloga zadržanosti:" 

Columns("G:G").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Prvi dan zadržanosti:" 

Columns("H:H").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Datum zadržanosti" 

Columns("I:I").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Datum zadržanosti do" 

Columns("J:J").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Znesek obračuna zavezanca:" 

Columns("K:K").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Znesek obračuna ZZZS:" 

Columns("L:L").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "Status obračuna:" 

Rows("1:1").Select 

With Selection 

    .HorizontalAlignment = xlGeneral 

    .VerticalAlignment = xlBottom 

    .WrapText = True 

    .Orientation = 0 

    .AddIndent = False 

    .IndentLevel = 0 

    .ShrinkToFit = False 

    .ReadingOrder = xlContext 

    .MergeCells = False 

End With 

Columns("I:I").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Columns("K:K").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Columns("G:G").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Range("A2").Select 

End Sub

Answer 1

When you insert 2 new columns into your table, they are named Column1 and Column2. But then you rename them both, so when you go to select Column1 again it does not exist.

Start at the top of your code and for every pair of lines:

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "new header text" 

make sure that you have 2 lines above it like this, though you would need to change the F:F to G:G etc....

Columns("F:F").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

Note that

Range("Refund[[#Headers],[Column1]]").Select 

ActiveCell.FormulaR1C1 = "new header text" 

can be re-written as

Range("Refund[[#Headers],[Column1]]").Value = "new header text" 

and

Columns("F:F").Select 

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 

as

Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Answer 2

Thank You Bernie,

The problem was that I made my macro on Excel with English Language Pack and want to run it on Excel with Slovenian Language Pack therefore I had to change coumn name "Column" to "Stolpec" and every thing works fine.

Thank You again for helping me find my own mistake.

BR Matjaž

Answer 3

Hi Matjaž

I noticed this is a recorded macro and these macros should be rewritten.

Please avoid using SELECT, SELECTION, or ACTIVECELL, it is slow and error-prone.

Always refer to the objects directly.

To further help you:

I would suggest the macro to be rewritten in the following order

First: Insert all the 15 Table Headers

Then: Convert the range to a Table and Rename it

The following macro will do it:

Sub CreateRefundTable()

'

' Keyboard Shortcut: Ctrl+Shift+A

With ActiveSheet

        With Rows("1:1") 

                .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 

               .HorizontalAlignment = xlGeneral 

               .VerticalAlignment = xlBottom 

               .WrapText = True 

               .Orientation = 0 

               .AddIndent = False 

               .IndentLevel = 0 

               .ShrinkToFit = False 

               .ReadingOrder = xlContext 

               .MergeCells = False 

        End With 

    '''' Insert all the table Headers 

.Range("A1").Value = "SPOT" 

.Range("B1").Value = "ZZZS številka obracuna" 

.Range("C1").Value = "ZZZS številka zavarovanca:" 

.Range("D1").Value = "Priimek in ime:" 

.Range("E1").Value = "Številka ePotrdila:" 

.Range("F1").Value = "Šifra razloga zadržanosti:" 

.Range("G1").Value = "Prvi dan zadržanosti:" 

.Range("H1").Value = "Datum zadržanosti" 

.Range("I1").Value = "Datum zadržanosti do" 

.Range("J1").Value = "Znesek obracuna zavezanca:" 

.Range("K1").Value = "Znesek obracuna ZZZS:" 

.Range("L1").Value = "Status obracuna:" 

.Range("M1").Value = 1 

.Range("N1").Value = 2 

.Range("O1").Value = 3 

''' Create the Table and Name it. 

.ListObjects.Add(xlSrcRange, Range("$A$1:$O$13051"), , xlYes).Name = "Refund" 

End With

End Sub

Note:

> You may easily change the order of the table headers according to your needs.

> Please, notice that we changed Range("$A$1:$D$13051") to Range("$A$1:$O$13051") as we have 15 columns range A to O.

Your table will look like this one

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 4

Hi

I hope you are OK

I'm writing to follow up on this thread because we haven't received any news from you in days

May I know if you need further help?

Did you solve your problem?

Did the reply/solution solve your problem?

Any updates will be welcome.

Regards

Jeovany