Need help..thinking maybe bloating?

[tx_tso]

Hi all. I am new to this forum and hope my first problem is not my last....so to speak here is the jist of my problem and i really really look forward to someone being able to help me!

i can add the attachemnts, but i am going to try and "spell" this out as best i can because i am not able to replicate my problem 100% of the time. go figure even though the process remains the same, the results are sometimes different...lol

i started this process a year plus ago and was orgianlly doing it in excel 2003. we have recently moved to 2007 some couple of months ago.

i have a workbook (call it WB1). in this workbook, i have some 20 worksheets (call them WS1-WS20). this file SHOULD only be mabye 100k max but is acutally 400k+. i am ok with this file size as it's only ONE file.

now everyday i run a process against my WB1. my macro takes each worksheet > WS4 and then A) coppies it into a whole NEW workbook and then saves that NEW workbook as it's worksheet's name. So, if i coppied WS7 then the new workbook it created with WS7 would be called WS7.

at anyrate, each NEW workbook it creates also becomes friggin bloated to 400k+. as you can guess, this starts to add up FAST!.

i have tried to right click on one of the WS's in the WB1, copy, put into new workbook and save it manually and i get a file that is either 16k or 400k.

WHAT THE ????!!! I KNOW the files should be between 10k and 25k but they are now ALL coming out to be 400k+.

part of my vba code that i use to copy/paste is listed below. i created a brand new workbook this year and re-coppied all the data in it to try and get rid of my bloating but alas, same problem.

anyone got any ideas? oh, sorry i am not sure how to seperate the code below so that it looks like code vs a post...


For Each wkSheet In CurWkbook.Worksheets
If wkSheet.Index < 4 Then
'do nothing
Else
shtcnt(1) = (6)

wkSheetName = Trim(wkSheet.Name) 'get name of worksheet
wkSheetName = wkSheetName & " " & dtimestamp 'add date stamp to worksheet name
Set newWkbook = ActiveWorkbook
Application.DisplayAlerts = False

On Error Resume Next
On Error GoTo 0

CurWkbook.Activate
CurWkbook.Worksheets(wkSheet.Name).Copy
ActiveWorkbook.SaveAs Filename:=xpathname & wkSheetName & ".xlsx" ', FileFormat:=51
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Next wkSheet

Application.StatusBar = False 'return control to Excel

For Each ws In Worksheets
ws.Activate
ActiveSheet.Unprotect
Next ws
Sheets("checks-n-balances").Select
Sheets("checks-n-balances").Range("A1").Select

Application.ScreenUpdating = True
'--------------------------------------------------------------------

 

[tx_tso]

There wouldn't happen to be images, shapes, or buttons or anything of that nature in your worksheets, would there? We should be able to generalize your solution just a bit more - are all the sheets the same size or is A1:I1000 simply a large enough size to cover all your data (and then some)?

ξ

um, yeah all the sheets are about the same. the are simple order forms for the most part. A1:I500 would be a better fit but i left room to grow.

no images. no graphs. just plain-jane text. there are some bits that are bold and i think there is a 4x3 area that is shaded yellow, but otherwise...pretty basic.

 

[tx_tso]

i am just going to chalk this up to microshaft giving me the finger