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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What error do you get? Are you sure that there is a worksheet with that name in the active workbook?
 

agvv

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jun 4, 2010
Messages
7

ADVERTISEMENT

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

New Member
Joined
Jun 4, 2010
Messages
7
pulled out the buttons. Still getting the error. The top of the error box references Microsoft Visual Basic.
 

agvv

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

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

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
Top