Stop Macro running when I save the workbook

T

TyreDude

Hi,

Please can you help, everytime I select save as on my excel
spreadsheet a macro runs which deletes all the values in the cell. I
need to keep the marco but I do not want it to run when I select save
as:

The macro should only run when a drop down list is changed which
populates data in the spreadsheet. The macro is not in a module but
resides in the workbook. How can I stop this macro running when I
select save as?

Private Sub Cash_Select_Change()

myYear = Year(Date)
LeapYear = "No"

If myYear = "2012" Or myYear = "2016" Or myYear = "2020" Or myYear =
"2024" Or myYear = "2028" Or myYear = "2032" Then
LeapYear = "Yes"
Else
LeapYear = "No"
End If


'Range("CURRENCY_1_FORECAST").ClearContents
'Range("CURRENCY_2_FORECAST").ClearContents
'Range("CURRENCY_3_FORECAST").ClearContents

If Cash_Forecast_Select.Value = "January" Then
Sheets("Cash Forecasting").Select
Range("January").Copy
Range("B17").Select
ActiveSheet.Paste
Range("B26").Select
ActiveSheet.Paste
Range("B35").Select
ActiveSheet.Paste
'Range("B44").Select
'ActiveSheet.Paste
'Range("B53").Select
'ActiveSheet.Paste
'Range("B62").Select
'ActiveSheet.Paste
Range("C19").Select
End If

End Sub

Thanks for any advice.

Brett
 
D

Don Guillett

Hi,

Please can you help, everytime I select save as on my excel
spreadsheet a macro runs which deletes all the values in the cell.  I
need to keep the marco but I do not want it to run when I select save
as:

The macro should only run when a drop down list is changed which
populates data in the spreadsheet.  The macro is not in a module but
resides in the workbook.  How can I stop this macro running when I
select save as?

Private Sub Cash_Select_Change()

myYear = Year(Date)
LeapYear = "No"

If myYear = "2012" Or myYear = "2016" Or myYear = "2020" Or myYear =
"2024" Or myYear = "2028" Or myYear = "2032" Then
LeapYear = "Yes"
Else
LeapYear = "No"
End If

'Range("CURRENCY_1_FORECAST").ClearContents
'Range("CURRENCY_2_FORECAST").ClearContents
'Range("CURRENCY_3_FORECAST").ClearContents

If Cash_Forecast_Select.Value = "January" Then
Sheets("Cash Forecasting").Select
Range("January").Copy
Range("B17").Select
ActiveSheet.Paste
Range("B26").Select
ActiveSheet.Paste
Range("B35").Select
ActiveSheet.Paste
'Range("B44").Select
'ActiveSheet.Paste
'Range("B53").Select
'ActiveSheet.Paste
'Range("B62").Select
'ActiveSheet.Paste
Range("C19").Select
End If

End Sub

Thanks for any advice.

Brett

You should write your macros to avoid selections. I think you want
this in a worksheet_change event in the SHEET module tied to the drop
down cell. I see other errors.

"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 

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

Top