Excel Macros for Beginners

B

BisyB

I'm fairly familiar with excel and am designing an estimatin
spreadsheet. I've tried to look through the thousands of posts for th
answers to these questions but have not found them so if anyone know
where they are hidden, that would be helpful. Thanks.

1. Is there a way to prompt an immediate "Save As" when opening
file?

2. I've seen VBA "buttons" that solve complicated calcs, however ca
you make a button that is programmed to auto print like 3 of
worksheets without going into the print box and picking them ou
individually?

3. On my first spreadsheet I have a list of about 250 items that ar
assigned a price and start with 0 quantities. As a bid is entered i
there a way to send items that have a quantity greater than 0 t
another worksheet?

Not all jobs use every item so everytime I enter the items I delet
each row I dont use and it is very time consuming. This is what starte
my search
 
S

superkopite

Q2

this will print a worksheet, just adapt it to your needs

Sub PrintWs()

Sheets("ENTER_SHEET_NAME").PageSetup.PrintArea = $A$1:$G$23 (inser
your own range)
Sheets("ENTER_SHEET_NAME").PrintOut

End Sub

Just add the other sheets you need to print int the same way.

Can you give a bit more detail about Q3

Regards

Jame
 
P

PipTT

Q1:

- In the Visual Basic Editor in the project window there's an objec
entitled "ThisWorkbook". If you double click this a script windo
appears. At the top of the page are two drop down boxes (mine say b
default "(General)" and "(Declarations)").
- Expand the left hand box and click 'Workbook' a private sub wil
appear, ignore it and/or delete it later.
- Expand the right hand box. In this there a lots of event. Man
useful ones. The one you want is the 'open' event. Click this an
another sub will appear:

Private Sub Workbook_Open()

End Sub


Q3:
Probably similar to above but use the 'SheetCalculate' or 'SheetChange
event, then right a macro. Something like:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If cells(x,y).value > 0 Then
Cells(x,y).select
Selection.copy
Workbooks.open("workbookname")
windows("workbookname").Activate
Cells(x1,y1).select
ActiveSheet.Paste
End If
End Su
 
J

JMB

Q1: Put this in the Thisworkbook module

Private Sub Workbook_Open()
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Q2: I think has been answered

Q3: A little unclear. Do you want the data transferred right after you key
each number in (would require worksheet_change event)? This would seem to
create a lot of overhead (copying a line after each time you enter a number)
- and what would happen if you went back and changed a number? You can use
Autofilter at the end to remove the zero values from your table, then copy
paste the filtered table to a different worksheet (Data/Filter/Autofilter -
then click the arrow above the column, choose custom and does not equal 0).
Is your table of 250 items the only thing on your source sheet?

Just as an example (given my limited knowledge of how your data is
organized) you could use code to perform the autofilter (where Sheet1 is the
source, Sheet2 is the destination-change if you need to, CriteriaCol is the
column you're analyzing, again change if you need to - also this assumes your
source worksheet has headers, nothing is in Sheet2, CriteriaCol = 2). Sheet2
is cleared every time you run the macro before data is copied into it.

Be sure to back up before trying anything new.

Sub Macro1()
Const CriteriaCol As Long = 2

With Worksheets("Sheet1").Range("A1")
.AutoFilter
.AutoFilter Field:=CriteriaCol, Criteria1:="<>0"
End With

Worksheets("Sheet2").Cells.Clear
Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Sheet2").Range("A1")
Application.CutCopyMode = False
Worksheets("Sheet1").Range("A1").AutoFilter

End Sub
 
B

BisyB

Hey thanks for the help guys. I've been playing with the modules and
working out a few of the kinks but so far the answers you gave seem to
be getting me on the right track.
 
Top