Multiple Worksheets Make HUGE file

[Guest]

I have a macro code that separates out data from warehouses that import in
one document. Example, a report has 3 warehouses in it, after formatting is
completed the macro creates a worksheet, listing the 3 warehouses, then from
that it will select warehouses from the single file, filter for the data
specific to them, copy the data and move it into a new worksheet. The wierd
thing (maybe not so wierd, I'm just naieve) is that the finished product is
HUGE, and shouldn't be. The code I use to copy data is:

' worksheet of the warehouses in document is created before this
' Worksheet "MASTER" is the one that ALL the data is in

Worksheets("WAREHOUSES").Activate
Range("A1").Select
GoTo Line4

Line3:
Worksheets("WAREHOUSES").Activate
ActiveCell.Offset(1, 0).Select

Line4:
If (ActiveCell = "") Then
GoTo Line5
Else
MyValue = ActiveCell.Value
Worksheets("MASTER").Select
Range("A:A").Select

Selection.AutoFilter Field:=1, Criteria1:=MyValue
Cells.Select

Selection.Copy
Sheets.Add
ActiveSheet.Name = MyValue
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Selection.EntireColumn.AutoFit
Selection.Sort Key1:=Range("W2"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With


GoTo Line3
End If

Line5:
Worksheets("WAREHOUSES").Select
ActiveWindow.SelectedSheets.Delete

Now I know that there are probably easier ways to work around something like
what I'm trying to do, but with the way the data comes in from the system,
this was the easiest method I could come up with. Any ideas why the file
might be so darn huge with only 4 worksheets in the end? THANKS!!!

 

[Evil Bumblebee]

Hi All,

This is how you put it next to an item in the custom made menu:

Sub check_Received()

Dim mypopup As CommandBarPopup
Set mypopup = CommandBars(1).Controls("My menu")

If mypopup.Controls("Received").State = msoButtonDown Then
'menu item unchecked
mypopup.Controls("Received").State = msoButtonUp
Else
'menu item checked
mypopup.Controls("Received").State = msoButtonDown
End If
End Sub

How do you go about an item in the submenu of the same menu (("My
menu")?

Cheers

 

[Evil Bumblebee]

Can it be that nobody in this group knows the reply to this question?