Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro Help!
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
MAB MAB is offline
external usenet poster
  
Posts: 40
Default Macro Help!
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
----------------------
end code:

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Macro Help!
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" wrote in message
...
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
----------------------
end code:



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Macro Help!
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" wrote in message
...
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
----------------------
end code:



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro recorded... tabs & file names changed, macro hangs Steve Excel Worksheet Functions 3 October 30th 09 11:41 AM
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
how to count/sum by function/macro to get the number of record to do copy/paste in macro tango Excel Programming 1 October 15th 04 01:16 PM
macro to delete entire rows when column A is blank ...a quick macro vikram Excel Programming 4 May 3rd 04 08:45 PM
Start Macro / Stop Macro / Restart Macro Pete[_13_] Excel Programming 2 November 21st 03 05:04 PM


All times are GMT +1. The time now is 07:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement