Macro periodically fails to recognize a worksheet

[agvv]

I have a multiple worksheet file that I load data into one, and it moves data around to a few different worksheets and then generates pivot tables and graphs in different worksheets. When I open the initial shell file and cut and paste data into the file, the macro works fine. But sporadically, when I cut and paste additional data into the file to generate refreshed tables and graphs, the macro fails. And it fails 3 lines into the macro and at would seem to be a very elementary level ---which is at Sheets("Copy data here").Select. The macro may work five times in a row, and then just stop getting past this point. Any ideas?

Macro1 Macro
'
Sheets("Prior Data Runs").Select
Range("a30").Select
Sheets("Copy data here").Select

 

[Andrew Poulsom]

What error do you get? Are you sure that there is a worksheet with that name in the active workbook?

 

[agvv]

I am certain the sheet exists. I don't make any changes to the worksheet names. I have even tried renaming the sheet, making certain the macro is adjusted for the modified sheet name, and it still fails at that third line into the macro. When I initially open the shell file and paste the first set of data, the macro never has a problem, always recognizes the worksheet. Two, sometimes three, sometimes four pastes later, the macro fails.

The error I get is:

Run-time error '-2147319765 (80002802b)':
Automation error
Element not found

 

[Andrew Poulsom]

That's not an error that I've seen in Excel. But it seems to relate to ActiveX controls. Do you have any of those? And where is the code that is causing the error?

 

[agvv]

You may be onto someting. I do "run" the macro from a button I inserted in the spreadsheet. Actually I inserted two buttons (one on one worksheet, the other on another) that each trigger the macro. Perhaps a bad idea? As far as where is the code, do you mean the macro code? Isn't it just part of the file itself? Thanks for your patience.

 

[agvv]

pulled out the buttons. Still getting the error. The top of the error box references Microsoft Visual Basic.

 

[Andrew Poulsom]

Can you post your macro please?

 

[agvv]

You are a brave man. When I choose Debug, the third line of the macro is highlighted.

Macro1 Macro
'
Sheets("Prior Data Runs").Select
Range("a30").Select
Sheets("Copy data here").Select
Range("r30").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Sheets("Prior Data Runs").Select
Range("a4").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B30").Select
Sheets("#-## thru #-##").Select
Columns("a:bh").Select
Selection.EntireColumn.Hidden = False
Sheets("#-## thru #-##").Select
Range("b21").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Range("b103").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("v95").Select
Selection.End(xlToLeft).Select
ActiveCell.EntireColumn.Select
Selection.Copy
Range("y1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("Ae1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("Ak1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("Aq1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("Aw1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("Bc1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("Bi1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("Bo1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("Bu1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("ca1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("cg1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("cm1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("cq16:cq89").Select
Selection.Copy
Range("aa16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("aa16:aa89").Select
Selection.Copy
Range("Ae16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("ae16:ae89").Select
Selection.Copy
Range("ak16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("ak16:ak89").Select
Selection.Copy
Range("aq16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("aq16:aq89").Select
Selection.Copy
Range("aw16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("aw16:aw89").Select
Selection.Copy
Range("bc16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("bc16:bc89").Select
Selection.Copy
Range("bi16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("bi16:bi89").Select
Selection.Copy
Range("bo16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("bo16:bo89").Select
Selection.Copy
Range("bu16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("bu16:bu89").Select
Selection.Copy
Range("ca16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("ca16:ca89").Select
Selection.Copy
Range("cg16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("cg16:cg89").Select
Selection.Copy
Range("cm16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("e1:Ad1").Select
Selection.EntireColumn.Hidden = True
Range("Ai1:Aj1").Select
Selection.EntireColumn.Hidden = True
Range("Ao1:Ap1").Select
Selection.EntireColumn.Hidden = True
Range("Au1:Av1").Select
Selection.EntireColumn.Hidden = True
Range("BA1:Bb1").Select
Selection.EntireColumn.Hidden = True
Range("Bg1:Bh1").Select
Selection.EntireColumn.Hidden = True
Range("Bm1:Bn1").Select
Selection.EntireColumn.Hidden = True
Range("Bs1:Bt1").Select
Selection.EntireColumn.Hidden = True
Range("By1:Bz1").Select
Selection.EntireColumn.Hidden = True
Range("ce1:cf1").Select
Selection.EntireColumn.Hidden = True
Range("ck1:cl1").Select
Selection.EntireColumn.Hidden = True
Range("A72").Select
'
End Sub