Excel 2003 error message!
 

Hello,

My workbook has 100 worksheets and I'm using the code below to makes to
all of them:

Sub EquipListFFPageSort()
'
Dim sh As Worksheet
Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Worksheets
sh.Select
'Your format code
Range("A1:C65536").Select
Application.Run
"EQUIP_LIST_Macros.xls!DeleteBlankSpacesOnTheLeftA ndRight"
Range("C4:C65536").Select
Selection.Copy
Range("D4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D65530").Select
ActiveCell.FormulaR1C1 = "ABC"
Range("D65531").Select
ActiveCell.FormulaR1C1 = "DEF"
Range("D65532").Select
ActiveCell.FormulaR1C1 = "GHI"
Range("D65533").Select
ActiveCell.FormulaR1C1 = "JKL"
Range("D4:D65536").Select
Application.Run "EQUIP_LIST_Macros.xls!RemoveAlphas"
Range("A4:D65536").Select
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending,
Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("C4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Columns("D:D").Select
Selection.ClearContents
Range("A1").Select

Application.ScreenUpdating = True
Next sh
End Sub

But when I run this code it goes through about 15 worksheets and gives
me these two error messages:

Microsoft Excel Error Message
Excel cannot complete task with available resources. Choose less data
or close other applications.

After I close this error message it brings up this error message:

Microsoft Visual Basic Error Message
Run-time error '1004':
Paste method of Worksheet class failed

Then I click debug and ActiveSheet.Paste is highlighted in my code.

How can I get this code to run without causing any errors?

Thank you for your help in advance,
jfcby

Excel 2003 error message!
 

What is the macro supposed to do exactly. What you have will not work. You
have 100 sheets and you are using 56,536 cells time number of columns on each
sheet. You are going to grind excel to a halt with something like that. Also
you are making calls to macros in an external workbook which you do not
explain.

How much data do you have on each sheet?
How is each sheet supposed to be modified?
What do your external macros do?
--
HTH...

Jim Thomlinson


"jfcby" wrote:

Hello,

My workbook has 100 worksheets and I'm using the code below to makes to
all of them:

Sub EquipListFFPageSort()
'
Dim sh As Worksheet
Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Worksheets
sh.Select
'Your format code
Range("A1:C65536").Select
Application.Run
"EQUIP_LIST_Macros.xls!DeleteBlankSpacesOnTheLeftA ndRight"
Range("C4:C65536").Select
Selection.Copy
Range("D4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D65530").Select
ActiveCell.FormulaR1C1 = "ABC"
Range("D65531").Select
ActiveCell.FormulaR1C1 = "DEF"
Range("D65532").Select
ActiveCell.FormulaR1C1 = "GHI"
Range("D65533").Select
ActiveCell.FormulaR1C1 = "JKL"
Range("D4:D65536").Select
Application.Run "EQUIP_LIST_Macros.xls!RemoveAlphas"
Range("A4:D65536").Select
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending,
Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("C4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Columns("D:D").Select
Selection.ClearContents
Range("A1").Select

Application.ScreenUpdating = True
Next sh
End Sub

But when I run this code it goes through about 15 worksheets and gives
me these two error messages:

Microsoft Excel Error Message
Excel cannot complete task with available resources. Choose less data
or close other applications.

After I close this error message it brings up this error message:

Microsoft Visual Basic Error Message
Run-time error '1004':
Paste method of Worksheet class failed

Then I click debug and ActiveSheet.Paste is highlighted in my code.

How can I get this code to run without causing any errors?

Thank you for your help in advance,
jfcby

Excel 2003 error message!
 

Hello Jim,

Thank you for your help! I changed 56,536 to 2000 which will work for
this workbook and it does not give me the error messages.

Thank you for you help,
jfcby


Jim Thomlinson wrote:
What is the macro supposed to do exactly. What you have will not work. You
have 100 sheets and you are using 56,536 cells time number of columns on each
sheet. You are going to grind excel to a halt with something like that. Also
you are making calls to macros in an external workbook which you do not
explain.

How much data do you have on each sheet?
How is each sheet supposed to be modified?
What do your external macros do?
--
HTH...

Jim Thomlinson


"jfcby" wrote:

Hello,

My workbook has 100 worksheets and I'm using the code below to makes to
all of them:

Sub EquipListFFPageSort()
'
Dim sh As Worksheet
Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Worksheets
sh.Select
'Your format code
Range("A1:C65536").Select
Application.Run
"EQUIP_LIST_Macros.xls!DeleteBlankSpacesOnTheLeftA ndRight"
Range("C4:C65536").Select
Selection.Copy
Range("D4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D65530").Select
ActiveCell.FormulaR1C1 = "ABC"
Range("D65531").Select
ActiveCell.FormulaR1C1 = "DEF"
Range("D65532").Select
ActiveCell.FormulaR1C1 = "GHI"
Range("D65533").Select
ActiveCell.FormulaR1C1 = "JKL"
Range("D4:D65536").Select
Application.Run "EQUIP_LIST_Macros.xls!RemoveAlphas"
Range("A4:D65536").Select
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending,
Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("C4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Columns("D:D").Select
Selection.ClearContents
Range("A1").Select

Application.ScreenUpdating = True
Next sh
End Sub

But when I run this code it goes through about 15 worksheets and gives
me these two error messages:

Microsoft Excel Error Message
Excel cannot complete task with available resources. Choose less data
or close other applications.

After I close this error message it brings up this error message:

Microsoft Visual Basic Error Message
Run-time error '1004':
Paste method of Worksheet class failed

Then I click debug and ActiveSheet.Paste is highlighted in my code.

How can I get this code to run without causing any errors?

Thank you for your help in advance,
jfcby