Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
I have this code that will:
a) create a new workbook
b) filter the source data in a second open workbook
c) copy the filtered results to the empty worksheet (ws_data) in the new workbook
The code runs well, however, the resulting new file is heavily bloated! With a (visible) range of data occupying A1:W71, the file is 20059kb in size. CTRL-END shows the last cell of the range to be W1048208.
Is anyone able to comment on what may be causing this bloat. It must be the way data is being copied and pasted between workbooks. The source file is only 149kb. Is there a better way to to this avoiding whatever is causing the bloat? If not, a means to reduce the size of the file to a more manageable one. Deleting the rows each time a new workbook is created may be an awkward and time consuming task, so I'd prefer to take a preventative approach rather than reactive if I could.
a) create a new workbook
b) filter the source data in a second open workbook
c) copy the filtered results to the empty worksheet (ws_data) in the new workbook
The code runs well, however, the resulting new file is heavily bloated! With a (visible) range of data occupying A1:W71, the file is 20059kb in size. CTRL-END shows the last cell of the range to be W1048208.
Is anyone able to comment on what may be causing this bloat. It must be the way data is being copied and pasted between workbooks. The source file is only 149kb. Is there a better way to to this avoiding whatever is causing the bloat? If not, a means to reduce the size of the file to a more manageable one. Deleting the rows each time a new workbook is created may be an awkward and time consuming task, so I'd prefer to take a preventative approach rather than reactive if I could.
Code:
For x = 1 To intCount '{2}
.Range("AH" & x) = DateValue(Right(.Range("AG" & x), 6))
'trgt_date = .Range("AH" & x)
trgt_date = "8/11/2016"
str_nwb = Format(trgt_date, "MMM-DD (DDD)") & " schedule_1.xlsx"
Workbooks.Add
With ActiveWorkbook
Sheets("Sheet1").Name = "DATA"
Sheets("Sheet2").Name = "STAFF"
Sheets("Sheet3").Name = "DEV"
.SaveAs "H:\PWS\Parks\Parks Operations\Sports\Sports17\DATA\" & str_nwb
Set wb_nwb = Workbooks(str_nwb)
Set ws_data = wb_nwb.Worksheets("DATA")
Set ws_staff = wb_nwb.Worksheets("STAFF")
Set ws_dev = wb_nwb.Worksheets("DEV")
End With
Windows(str_nwb).Visible = False
'filter database
With ws_sched
.Range("A1").AutoFilter _
Field:=2, _
Criteria1:=trgt_date, _
VisibleDropDown:=False
Set srng = .Cells.SpecialCells(xlCellTypeVisible)
srng.Copy ws_data.Range("A1")
If ws_sched.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
End With