Macro periodically fails to recognize a worksheet

agvv

New Member
Joined
Jun 4, 2010
Messages
7
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
pulled out the buttons. Still getting the error. The top of the error box references Microsoft Visual Basic.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,789
Messages
6,103,985
Members
447,888
Latest member
eaydogan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top