(run time error 1004)-Delete method of range class failed- VB based macro consolidation

Question

(run time error 1004)-Delete method of range class failed- VB based macro consolidation

Fauzia Naqvi 0

I dont know anything about VB. I consoidate timesheets end of the month which are excel based with VB coding. there are 10 excel timesheets when I click the macro button for internal consoidation , this time it is giving an erro

Runtime error 10004 Delete method of range class failed

and when i click debug, the following comes, I have no idea hot wot fix it, I should have completed this taks yesterady but struggling to fix the error. please help

In the below coding the error is this one in yello highlighted , I am copying here as is it not copying as a highlighted text

**Sheets("Consolidation EnC").Columns("P:U").Delete Shift:=xlToLeft

 Next
'-------------------------------------------------------------------
'End of Loop to download the data and store in new format database for EnC
'-------------------------------------------------------------------
    Sheets("Consolidation EnC").Activate
    Sheets("Consolidation EnC").Cells.Clear
    Sheets("Consolidation EnC").Cells(1, 1).Select
    Sheets("Consolidation EnC").Range(Cells(1, 1), Cells(V_loop2, 21)).Value = Tb_Manpower_temporary

    Sheets("Consolidation EnC").Columns("P:U").Delete Shift:=xlToLeft
   
    Sheets("Data").Rows("50:50").Copy
    Sheets("Consolidation EnC").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Consolidation EnC").Range("A1").Select
    
    Sheets("Consolidation EnC").Range(Cells(1, 1), Cells(Range("F65536").End(xlUp).Row, 15)).AutoFilter

    Sheets("Consolidation EnC").Range("A1").Select

'*******************************************************************
'-------------------------------------------------------------------
'Loop to download the data and store in new format database for FOPS

KOZ6.0 6,735 Reputation points

2023-10-28T08:26:22.4466667+00:00

You may be able to find out the cause by manually deleting the sheet that failed to be deleted.

Don't forget to back up.
Fauzia Naqvi 0 Reputation points

2023-10-28T09:10:25.46+00:00

the reffered file in the error is the main file where all data is consolidating , Impossible to delete that :( or if I delete how I get all the consolidated data for the full month ?
Barry Schwarz 3,746 Reputation points

2023-10-28T11:45:53.9733333+00:00

It seems very strange that the macro goes through the trouble to set cells (P,1) through (U,V_loop2) and then immediately deletes those same columns completely.

You might also want to consider trimming the tags you assigned the question to just Excel.

Your answer

KOZ6.0 6,735 Reputation points

2023-10-28T08:26:22.4466667+00:00

You may be able to find out the cause by manually deleting the sheet that failed to be deleted.

Don't forget to back up.
Fauzia Naqvi 0 Reputation points

2023-10-28T09:10:25.46+00:00

the reffered file in the error is the main file where all data is consolidating , Impossible to delete that :( or if I delete how I get all the consolidated data for the full month ?
Barry Schwarz 3,746 Reputation points

2023-10-28T11:45:53.9733333+00:00

It seems very strange that the macro goes through the trouble to set cells (P,1) through (U,V_loop2) and then immediately deletes those same columns completely.

You might also want to consider trimming the tags you assigned the question to just Excel.

Share via

(run time error 1004)-Delete method of range class failed- VB based macro consolidation

Your answer