Excel Macros for Beginners

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

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

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

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

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

Q2

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

Sub PrintWs()

Sheets("ENTER_SHEET_NAME").PageSetup.PrintArea = $A$1:$G$23 (insert 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

James

Q1:

- In the Visual Basic Editor in the project window there's an object entitled "ThisWorkbook". If you double click this a script window appears. At the top of the page are two drop down boxes (mine say by default "(General)" and "(Declarations)").
- Expand the left hand box and click 'Workbook' a private sub will appear, ignore it and/or delete it later.
- Expand the right hand box. In this there a lots of event. Many useful ones. The one you want is the 'open' event. Click this and 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 Sub

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

"BisyB" wrote:

>
> I'm fairly familiar with excel and am designing an estimating
> spreadsheet. I've tried to look through the thousands of posts for the
> answers to these questions but have not found them so if anyone knows
> where they are hidden, that would be helpful. Thanks.
>
> 1. Is there a way to prompt an immediate "Save As" when opening a
> file?
>
> 2. I've seen VBA "buttons" that solve complicated calcs, however can
> you make a button that is programmed to auto print like 3 of 8
> worksheets without going into the print box and picking them out
> individually?
>
> 3. On my first spreadsheet I have a list of about 250 items that are
> assigned a price and start with 0 quantities. As a bid is entered is
> there a way to send items that have a quantity greater than 0 to
> another worksheet?
>
> Not all jobs use every item so everytime I enter the items I delete
> each row I dont use and it is very time consuming. This is what started
> my search.
>
>
> --
> BisyB
> ------------------------------------------------------------------------
> BisyB's Profile: http://www.excelforum.com/member.php...o&userid=30991
> View this thread: http://www.excelforum.com/showthread...hreadid=506557
>
>