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!!!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

MarkHenri

Board Regular
Joined
Aug 2, 2002
Messages
106
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.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
To see all Excel limitations/specifications, go to Excel's Help/Contents and Index menu, and type in "Specifications" (without the apostrophes).

Regards,

Mike
 

cjp12711

New Member
Joined
Sep 12, 2002
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,018
Messages
5,569,646
Members
412,285
Latest member
Daibear
Top