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.


--
BisyB
------------------------------------------------------------------------
BisyB's Profile: http://www.excelforum.com/member.php...o&userid=30991
View this thread: http://www.excelforum.com/showthread...hreadid=506557

Excel Macros for Beginners
 

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


--
superkopite
------------------------------------------------------------------------
superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=506557

Excel Macros for Beginners
 

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


--
PipTT
------------------------------------------------------------------------
PipTT's Profile: http://www.excelforum.com/member.php...o&userid=30989
View this thread: http://www.excelforum.com/showthread...hreadid=506557

Excel Macros for Beginners
 

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(xlCellType Visible).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

Excel Macros for Beginners
 

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.


--
BisyB
------------------------------------------------------------------------
BisyB's Profile: http://www.excelforum.com/member.php...o&userid=30991
View this thread: http://www.excelforum.com/showthread...hreadid=506557