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

Fauzia Naqvi 0 Reputation points
2023-10-28T07:34:47.64+00:00

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
Microsoft 365
Microsoft 365
Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
2,712 questions
Microsoft Teams
Microsoft Teams
A Microsoft customizable chat-based workspace.
8,165 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,104 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,410 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,564 questions