Macro Help!

G

Guest

I inherited a worksheet here at work, and supervision asked me to add
something to an already existing macro.

They want the macro to start with a check that the workbook I&CRates.xls is
open. If it's not, then display a message box stating "Open the Rate
Calculation spreadsheet, then return to this workbook and try again." They'd
like the macro to end if the user clicks "Ok" or "cancel."

The only coding experience I have is copying and pasting into code. :-(

Any help would be appreciated.

Thank you!

Oh, if it matters, I'm using Excel 2003.

code:
----------------------
Sub CALCCREDIT()
'
' CALCCREDIT Macro
'

Windows("I&CRates.xls").Activate
Sheets("Customer Data").Select

'Steps below will show the user the account name & number and allow the
info to be edited.
Windows("Billhist.xls").Activate
Sheets("Input Screen").Select
Dim Message, Title, Default, AcctNameValue
Message = "Acct Name - Revise if wrong" ' Set prompt.
Title = "Account Name" ' Set title.
Range("Account_Name").Select
Default = ActiveCell.Value ' Set account name =
what's on Input Sheet ' Set default.
'Display
message, title, and default value.
AcctNameValue = InputBox(Message, Title, Default) 'Display input
box, to confirm acct name
ActiveCell.Value = AcctNameValue

Dim AcctNumbValue
Message = "Acct Number - Revise if wrong" ' Set prompt.
Title = "Account Number" ' Set title.
Range("Account_Number").Select
Default = ActiveCell.Value ' Set account number =
what's on Input Sheet
'Display
message, title, and default value.
AcctNumbValue = InputBox(Message, Title, Default) 'Display input
box, to confirm acct number
ActiveCell.Value = AcctNumbValue

'Steps below will copy the appropriate data to the Rate Calc sprdsht.
Sheets("Input Screen").Select
Range("F10:I21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Sheets("Customer Data").Select
Range("A12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("L10:L21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("E12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("N10:N21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("F12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("Q10:Q21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("H12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("H5").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("I&CRates.xls").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Application.CutCopyMode = False
Range("R10:R21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("I12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Application.CutCopyMode = False
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("WFM Credit Calc").Select
Range("U45").Select

'Steps below will write formulas into the Calculated GS1 & GS3 columns,
'providing results without the sheet having formula links
initially.
Sheets("WFM Credit Calc").Select
ActiveSheet.Unprotect
Range("s10").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=0,"""",'[I&CRates.xls]GS1 Annual'!R[10]C[-13])"
Range("T10").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=0,"""",'[I&CRates.xls]GS3 Annual'!R[10]C[-14])"
Range("S10:T10").Select
Selection.Copy
Range("S11:T21").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("S10").Select
ActiveSheet.Protect


End Sub
 
B

Bob Phillips

Dim wb As Workbook
Dim ans

On Error Resume Next
Set wb = Workbooks("I&CRates.xls")
On Error Goto 0
If wb Is Nothing Then
ans = MsgBox("Open the Rate Calculation",vbYesNoCancel")
If ans = vbYes Then
Workbooks.Open Filename:="I&CRates.xls"
Else
Exit Sub
End If
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


MAB said:
I inherited a worksheet here at work, and supervision asked me to add
something to an already existing macro.

They want the macro to start with a check that the workbook I&CRates.xls is
open. If it's not, then display a message box stating "Open the Rate
Calculation spreadsheet, then return to this workbook and try again." They'd
like the macro to end if the user clicks "Ok" or "cancel."

The only coding experience I have is copying and pasting into code. :-(

Any help would be appreciated.

Thank you!

Oh, if it matters, I'm using Excel 2003.

code:
----------------------
Sub CALCCREDIT()
'
' CALCCREDIT Macro
'

Windows("I&CRates.xls").Activate
Sheets("Customer Data").Select

'Steps below will show the user the account name & number and allow the
info to be edited.
Windows("Billhist.xls").Activate
Sheets("Input Screen").Select
Dim Message, Title, Default, AcctNameValue
Message = "Acct Name - Revise if wrong" ' Set prompt.
Title = "Account Name" ' Set title.
Range("Account_Name").Select
Default = ActiveCell.Value ' Set account name =
what's on Input Sheet ' Set default.
'Display
message, title, and default value.
AcctNameValue = InputBox(Message, Title, Default) 'Display input
box, to confirm acct name
ActiveCell.Value = AcctNameValue

Dim AcctNumbValue
Message = "Acct Number - Revise if wrong" ' Set prompt.
Title = "Account Number" ' Set title.
Range("Account_Number").Select
Default = ActiveCell.Value ' Set account number =
what's on Input Sheet
'Display
message, title, and default value.
AcctNumbValue = InputBox(Message, Title, Default) 'Display input
box, to confirm acct number
ActiveCell.Value = AcctNumbValue

'Steps below will copy the appropriate data to the Rate Calc sprdsht.
Sheets("Input Screen").Select
Range("F10:I21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Sheets("Customer Data").Select
Range("A12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("L10:L21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("E12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("N10:N21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("F12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("Q10:Q21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("H12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("H5").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("I&CRates.xls").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Application.CutCopyMode = False
Range("R10:R21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("I12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Application.CutCopyMode = False
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("WFM Credit Calc").Select
Range("U45").Select

'Steps below will write formulas into the Calculated GS1 & GS3 columns,
'providing results without the sheet having formula links
initially.
Sheets("WFM Credit Calc").Select
ActiveSheet.Unprotect
Range("s10").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=0,"""",'[I&CRates.xls]GS1 Annual'!R[10]C[-13])"
Range("T10").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=0,"""",'[I&CRates.xls]GS3 Annual'!R[10]C[-14])"
Range("S10:T10").Select
Selection.Copy
Range("S11:T21").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("S10").Select
ActiveSheet.Protect


End Sub
Click to expand...
 
T

Tom Ogilvy

If you know where it is located, wouldn't it be more useful just to open it
if it isn't open

Sub CALCCREDIT()
'
' CALCCREDIT Macro
'
On Error Resume Next
Windows("I&CRates.xls").Activate
On Error goto 0
if err <> 0 then
workbooks.Open "M:\Common1\I&CRates.xls"
err.clear
end if
. . .

--
Regards,
Tom Ogilvy


MAB said:
I inherited a worksheet here at work, and supervision asked me to add
something to an already existing macro.

They want the macro to start with a check that the workbook I&CRates.xls is
open. If it's not, then display a message box stating "Open the Rate
Calculation spreadsheet, then return to this workbook and try again." They'd
like the macro to end if the user clicks "Ok" or "cancel."

The only coding experience I have is copying and pasting into code. :-(

Any help would be appreciated.

Thank you!

Oh, if it matters, I'm using Excel 2003.

code:
----------------------
Sub CALCCREDIT()
'
' CALCCREDIT Macro
'

Windows("I&CRates.xls").Activate
Sheets("Customer Data").Select

'Steps below will show the user the account name & number and allow the
info to be edited.
Windows("Billhist.xls").Activate
Sheets("Input Screen").Select
Dim Message, Title, Default, AcctNameValue
Message = "Acct Name - Revise if wrong" ' Set prompt.
Title = "Account Name" ' Set title.
Range("Account_Name").Select
Default = ActiveCell.Value ' Set account name =
what's on Input Sheet ' Set default.
'Display
message, title, and default value.
AcctNameValue = InputBox(Message, Title, Default) 'Display input
box, to confirm acct name
ActiveCell.Value = AcctNameValue

Dim AcctNumbValue
Message = "Acct Number - Revise if wrong" ' Set prompt.
Title = "Account Number" ' Set title.
Range("Account_Number").Select
Default = ActiveCell.Value ' Set account number =
what's on Input Sheet
'Display
message, title, and default value.
AcctNumbValue = InputBox(Message, Title, Default) 'Display input
box, to confirm acct number
ActiveCell.Value = AcctNumbValue

'Steps below will copy the appropriate data to the Rate Calc sprdsht.
Sheets("Input Screen").Select
Range("F10:I21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Sheets("Customer Data").Select
Range("A12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("L10:L21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("E12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("N10:N21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("F12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("Q10:Q21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("H12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("H5").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("A5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("I&CRates.xls").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Application.CutCopyMode = False
Range("R10:R21").Select
Selection.Copy
Windows("I&CRates.xls").Activate
Range("I12").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("Billhist.xls").Activate
Application.CutCopyMode = False
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("WFM Credit Calc").Select
Range("U45").Select

'Steps below will write formulas into the Calculated GS1 & GS3 columns,
'providing results without the sheet having formula links
initially.
Sheets("WFM Credit Calc").Select
ActiveSheet.Unprotect
Range("s10").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=0,"""",'[I&CRates.xls]GS1 Annual'!R[10]C[-13])"
Range("T10").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=0,"""",'[I&CRates.xls]GS3 Annual'!R[10]C[-14])"
Range("S10:T10").Select
Selection.Copy
Range("S11:T21").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("S10").Select
ActiveSheet.Protect


End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conso macro 0
Run-time error 1004 paste method of worksheet class failed 3
lenghty code 4
Run macro from active workbook whose name keeps changing 20
Help again!!! 1
= Today()+1 into Sheet1, Sheet2, and Sheet3 2
Code not working as expected 2
A better Macro for Copying Data 2

Top