Question: Excel Macro error

[usr7501]

I am trying to migrate code that was generated as recorded Excel macro to VB code. Can someone tell me why the interpreter stops at the enclosed row? I am using F8 to step and the error is generated at line: "Selection.Delete Shift:=xlUp".

I copied the code that was in a different work book to a new work book.

Excel says "error 1004". "Program/application or object defined error".

ActiveCell.Rows("1:1").EntireRow.Select
  Selection.Delete Shift:=xlUp
  ActiveCell.Offset(1, 0).Range("Tabelldata__4[[#Headers],[Column1]]").Select
  Selection.Copy

 

[NARAYANK991]

Hi ,

I tried out the first two lines of the code you posted in a new workbook ; no error was generated.

Can you post your workbook with all of the data and the complete code in it ?

Narayan

 

[usr7501]

Will the interpreter complain if the code refers to a different work sheet, if so how is that rectified?


This is my full code:

Sub Test()

ActiveCell.Rows("1:1").EntireRow.Select
  Selection.Delete Shift:=xlUp
  ActiveCell.Offset(1, 0).Range("Tabelldata__4[[#Headers],[Column1]]").Select
  Selection.Copy
  ActiveCell.Offset(-1, 2).Range("Tabelldata__4[[#Headers],[Column1]]").Select
  Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  False, Transpose:=True
  ActiveCell.Offset(1, -1).Range("Tabelldata__4[[#Headers],[Column1]]").Select
  Application.CutCopyMode = False
  Selection.Copy
  ActiveCell.Offset(-1, 2).Range("Tabelldata__4[[#Headers],[Column1]]").Select
  Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  False, Transpose:=True
  ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
  ActiveCell.Rows("1:1").EntireRow.Select
End Sub

 

[usr7501]

Anyone?

 

[NARAYANK991]

Hi ,

Can you post your workbook with all of the data and the complete code in it ?

Narayan

 

[usr7501]

Uploaded. Did I forget to config Excel or did I forget do change/update something that VB requires me to to avoid the 1004 error.

Kind regards,

 

[Debaser]

You can't delete the header row of a table, and this syntax is wrong:

    ActiveCell.Offset(1, 0).Range("Tabelldata[#Headers]").Select

I think it would be better to describe exactly what you want the code to do.

 

[NARAYANK991]

Hi ,

The attached file contains the revised macro , though it can be improved upon , especially to make it so that it can be run on any sheet.

At present , it will run on the worksheet where the macro is placed , so when running it on Blad1 , run the macro Blad1.Makro1.

Narayan

 

[usr7501]

Hi ,

The attached file contains the revised macro , though it can be improved upon , especially to make it so that it can be run on any sheet.

At present , it will run on the worksheet where the macro is placed , so when running it on Blad1 , run the macro Blad1.Makro1.

Narayan

Thanks and bless you.