Is there a max number of worksheets available?

cjp12711

New Member
Joined
Sep 12, 2002
Messages
2
I have a VBA program running behind Excel to look at an Access table. It will then divide the access table into different worksheets based on the value in column A. For some reason, I am getting an error when I run it, although there has been no real change to the code. It keeps pointing me to an error with copying worksheets. Is there a maximum number of worksheets that you can have? Does this error sound familiar to anyone else? Any help is greatly appreciated!!!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You are limited to the number of rows and columns per sheet. You get this error when your data exceeds that limit.

The number of sheets is not limited, but the number of sheets you can have is limited by the memory you have installed. JSW
 
Upvote 0
That's right about the worksheets. Other limits can be found in the help. Use the search word 'limits' and you'll find a page called specifications and limits. Very helpful.
 
Upvote 0
To see all Excel limitations/specifications, go to Excel's Help/Contents and Index menu, and type in "Specifications" (without the apostrophes).

Regards,

Mike
 
Upvote 0
It doesn't look like a matter of exceeding the number of rows or columns. There are no worksheets that have more than the 65,000 record limit. There are 70 different values in column A that need their own spreadsheet. Then, another problem is that it is not grouping correctly on the summary sheet. Instead of summing for all entries of "001", it is putting a line in several times. I will post the code below.



Option Explicit

Sub CreateHubSheets()
Dim thub As String
Dim lashub As String
Dim shenam As String
Dim wHub As Worksheet
Dim ir As Long
Dim first As Boolean

' Open Database
Set GDB = OpenDatabase("N:PPO RevenuePPORevenue1.mdb")
Set RST = GDB.OPENRECORDSET("DETAIL", dbOpenDynaset)
Reset

Set WSUM = CreateSheet("SUM", "TSUM")
ISROW = K_1STSUMROW

'Pass table and break on new hub number

first = True
Do While (Not RST.EOF)
If (IsNull(RST!HUBSITE)) Then
thub = "_Invalid"
Else
thub = RST!HUBSITE
End If
If (thub <> lashub) Then
If (Not first) Then
Update_Summary
ISROW = ISROW + 1
End If
first = False
shenam = "HUB" & thub
Set wHub = CreateSheet(shenam, "THUB")
lashub = thub
ir = K_1STHUBROW
WSUM.Cells(ISROW, K_SUMHUBNUMCOL) = thub
WSUM.Cells(ISROW, K_SUMHUBNAMCOL) = RST!SITE_NAME
End If
wHub.Cells(ir, K_HUBNUMCOL) = RST!HUBSITE
wHub.Cells(ir, K_HUBNAMCOL) = RST!SITE_NAME

wHub.Cells(ir, K_HUBMACNUMCOL) = RST!MACHINE
wHub.Cells(ir, K_HUBMACNAMCOL) = RST!machname
wHub.Cells(ir, K_HUBSTACOL) = RST!ST
wHub.Cells(ir, K_HUBCONNUMCOL) = RST!CONTRACT#
wHub.Cells(ir, K_HUBBATNUMCOL) = RST!BATCH
wHub.Cells(ir, K_HUBSHENUMCOL) = RST!SHEET
wHub.Cells(ir, K_HUBCHAAMTCOL) = RST!CHARGE
wHub.Cells(ir, K_HUBSTDREDCOL) = RST!PPOSTDREDUCT
wHub.Cells(ir, K_HUBPPOSAVCOL) = RST!PPOSAVINGS
wHub.Cells(ir, K_HUBPPOREVCOL) = RST!PPOREVENUE
wHub.Cells(ir, K_HUBSAVPCTCOL).Formula = "=RC[-2] / ( RC[-4] - RC[-3])"
ir = ir + 1
TOTCHARGE = TOTCHARGE + RST!CHARGE
TOTPPOSTDREDUCT = TOTPPOSTDREDUCT + RST!PPOSTDREDUCT
TOTPPOSAVINGS = TOTPPOSAVINGS + RST!PPOSAVINGS
TOTPPOREVENUE = TOTPPOREVENUE + RST!PPOREVENUE
RST.MoveNext
Loop

Update_Summary
ISROW = ISROW + 1
WSUM.Cells(ISROW, K_SUMCHAAMTCOL) = GTOTCHARGE
WSUM.Cells(ISROW, K_SUMSTDREDCOL) = GTOTPPOSTDREDUCT
WSUM.Cells(ISROW, K_SUMPPOSAVCOL) = GTOTPPOSAVINGS
WSUM.Cells(ISROW, K_SUMPPOREVCOL) = GTOTPPOREVENUE
WSUM.Cells(ISROW, K_SUMSAVPCTCOL).Formula = "=RC[-2] / ( RC[-4] - RC[-3])"
Sheets("SUM").Select

RST.Close

End Sub

' Delete old / Create new worksheet based on master
'--------------------------------------------------

Function CreateSheet(xSheetName, xType) As Worksheet

Dim wnew As Worksheet
Dim wmaster As Object
Dim sheetname As String

Set wmaster = Sheets(xType)
sheetname = xSheetName
Application.DisplayAlerts = False
On Error Resume Next
Sheets(sheetname).Delete
On Error GoTo 0
Application.DisplayAlerts = True

wmaster.Copy Before:=Sheets("THUB")
Set wnew = ActiveSheet
wnew.Name = sheetname

Set CreateSheet = wnew

End Function
' Reset
'-------------------------------------------------------

Sub Reset()

Dim WD As Worksheet
Dim ir As Long

For Each WD In Worksheets
If (Left(WD.Name, 3) = "HUB" Or Left(WD.Name, 3) = "SUM") Then
Application.DisplayAlerts = False
On Error Resume Next
WD.Delete
On Error GoTo 0
Application.DisplayAlerts = True
End If
Next WD

End Sub
'
'Update Summary Sheet
'-----------------------------------------------------------

Sub Update_Summary()
WSUM.Cells(ISROW, K_SUMCHAAMTCOL) = TOTCHARGE
WSUM.Cells(ISROW, K_SUMSTDREDCOL) = TOTPPOSTDREDUCT
WSUM.Cells(ISROW, K_SUMPPOSAVCOL) = TOTPPOSAVINGS
WSUM.Cells(ISROW, K_SUMPPOREVCOL) = TOTPPOREVENUE
WSUM.Cells(ISROW, K_SUMSAVPCTCOL).Formula = "=RC[-2] / ( RC[-4] - RC[-3])"
GTOTCHARGE = GTOTCHARGE + TOTCHARGE
GTOTPPOSTDREDUCT = GTOTPPOSTDREDUCT + TOTPPOSTDREDUCT
GTOTPPOSAVINGS = GTOTPPOSAVINGS + TOTPPOSAVINGS
GTOTPPOREVENUE = GTOTPPOREVENUE + TOTPPOREVENUE
TOTCHARGE = 0
TOTPPOSTDREDUCT = 0
TOTPPOSAVINGS = 0
TOTPPOREVENUE = 0

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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