Intuitive Macro that changes based on cell value

[Don]

I don't know if this is possible but I am hoping so. I run several
performance reports daily and weekly. On Monday's I have to run the
reports twice, once to compile the prior week recap and the second
time to produce the current weeks performance. Currently between runs,
I have to archive the prior files and reset the data sources.
Basically, I open Data2 and resave it as Data1, so the macro will look
to the correct data source. In my Report Dashboard I have a fiscal
week indicator, currently 35.

I have a macro that will save the prior weeks files with a reporting
week in the title.

EXAMPLE:
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\Report_WK34.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The code works fine but of course I have to manually change the week
number each week. I am looking for a macro that would look at the
fiscal week indicator in my dashboard

Sheets("Dashboard").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "35"

and change the macro to match the indicator Dashboard, Cell C1, so I
can eliminate this manual process.

Is this even possible and if so, HELP! How can I achieve this?

 

[GS]

Don formulated the question :

I don't know if this is possible but I am hoping so. I run several
performance reports daily and weekly. On Monday's I have to run the
reports twice, once to compile the prior week recap and the second
time to produce the current weeks performance. Currently between runs,
I have to archive the prior files and reset the data sources.
Basically, I open Data2 and resave it as Data1, so the macro will look
to the correct data source. In my Report Dashboard I have a fiscal
week indicator, currently 35.

I have a macro that will save the prior weeks files with a reporting
week in the title.

EXAMPLE:
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\Report_WK34.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The code works fine but of course I have to manually change the week
number each week. I am looking for a macro that would look at the
fiscal week indicator in my dashboard

Sheets("Dashboard").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "35"

and change the macro to match the indicator Dashboard, Cell C1, so I
can eliminate this manual process.

Is this even possible and if so, HELP! How can I achieve this?

How about...

ActiveWorkbook.SaveAs Filename:= "P:\Finance\central\Finance\Daily\"
_
& "Report_WK" & CStr(Sheets("Dashboard").Range("$C$1")) & ".xls"

Note that all the other args are defaults and so need not be included
in your code.

 

[Clif McIrvin]

I don't know if this is possible but I am hoping so. I run several
performance reports daily and weekly. On Monday's I have to run the
reports twice, once to compile the prior week recap and the second
time to produce the current weeks performance. Currently between runs,
I have to archive the prior files and reset the data sources.
Basically, I open Data2 and resave it as Data1, so the macro will look
to the correct data source. In my Report Dashboard I have a fiscal
week indicator, currently 35.

I have a macro that will save the prior weeks files with a reporting
week in the title.

EXAMPLE:
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\Report_WK34.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The code works fine but of course I have to manually change the week
number each week. I am looking for a macro that would look at the
fiscal week indicator in my dashboard

Sheets("Dashboard").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "35"

and change the macro to match the indicator Dashboard, Cell C1, so I
can eliminate this manual process.

Is this even possible and if so, HELP! How can I achieve this?

You're saying that Dashboard!C1 (Cell C1 of Sheet "Dashboard") is your
fiscal week indicator?

Here's a bit of air code:

Sub Sample1()

Const cstrFiscalWeekAddr As String = "Dashboard!C1"
Dim strFiscalWeek As String
Dim strFilename As String

strFiscalWeek = Format(Range(cstrFiscalWeekAddr), "0")
strFilename = _
"P:\Finance\central\Finance\Daily\Report_WK" _
& strFiscalWeek & ".xls"

ActiveWorkbook.SaveAs Filename:=strFilename _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

 

[Don]

Don formulated the question :












How about...

  ActiveWorkbook.SaveAs Filename:= "P:\Finance\central\Finance\Daily\"
_
     & "Report_WK" & CStr(Sheets("Dashboard").Range("$C$1")) & ".xls"

Note that all the other args are defaults and so need not be included
in your code.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Garry,

I appreciate the assistance, unfortunately I am getting a VB error

Here is exactly the code I am using (the line above, the relevant
line, and the line below.

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\" &
"Plant_Premium_Heat_Map_WK" & CStr(Sheets("Dashboard").Range("$C$1"))
& ".xls"
ActiveWindow.Close

 

[Don]

You're saying that Dashboard!C1 (Cell C1 of Sheet "Dashboard") is your
fiscal week indicator?

Here's a bit of air code:

Sub Sample1()

Const cstrFiscalWeekAddr As String = "Dashboard!C1"
Dim strFiscalWeek As String
Dim strFilename As String

strFiscalWeek = Format(Range(cstrFiscalWeekAddr), "0")
strFilename = _
   "P:\Finance\central\Finance\Daily\Report_WK" _
   & strFiscalWeek & ".xls"

 ActiveWorkbook.SaveAs Filename:=strFilename _
 , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
 ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket - Hide quoted text -

- Show quoted text -

Cliff,

I also tried you solution and got an error as well on the 6th line
shown below strFiscalWeek = Format(Range(cstrFiscalWeekAddr), "0")

Application.DisplayAlerts = False
Const cstrFiscalWeekAddr As String = "Dashboard!C1"
Dim strFiscalWeek As String
Dim strFilename As String

strFiscalWeek = Format(Range(cstrFiscalWeekAddr), "0")
strFilename = _
"P:\Finance\central\Finance\Daily\Plant_Premium_Heat_Map_WK" _
& strFiscalWeek & ".xls"

ActiveWorkbook.SaveAs Filename:=strFilename _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

 

[Clif McIrvin]

[ ]
Cliff,

I also tried you solution and got an error as well on the 6th line
shown below strFiscalWeek = Format(Range(cstrFiscalWeekAddr), "0")
------------

I did note that this was air code ... meaning you need to do testing.

Assumptions:
Dashboard!C1 is a legal address from the code that is running.
The contents of C1 is a number.

Just telling us you get an error is not enough information for us to
help you further ... at the least provide the actual text of the error
message .... better yet, use the VBE debugger and report the contents of
the relevant variables.

From the debugger window go to the immediate window (ctrl+g) and type
?cstrFiscalWeekAddr
?Range(cstrFiscalWeekAddr)

and see what is printed. If that doesn't get you going, post back with
the actual error message(s) and the results of the immediate print
statements above.

--
Clif

Application.DisplayAlerts = False
Const cstrFiscalWeekAddr As String = "Dashboard!C1"
Dim strFiscalWeek As String
Dim strFilename As String

strFiscalWeek = Format(Range(cstrFiscalWeekAddr), "0")
strFilename = _
"P:\Finance\central\Finance\Daily\Plant_Premium_Heat_Map_WK" _
& strFiscalWeek & ".xls"

ActiveWorkbook.SaveAs Filename:=strFilename _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

 

[Clif McIrvin]

Don formulated the question :

[ ]
Garry,

I appreciate the assistance, unfortunately I am getting a VB error

Here is exactly the code I am using (the line above, the relevant
line, and the line below.

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\" &
"Plant_Premium_Heat_Map_WK" & CStr(Sheets("Dashboard").Range("$C$1"))
& ".xls"
ActiveWindow.Close

--------

Try
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\" &
"Plant_Premium_Heat_Map_WK" & _
CStr(Sheets(""""Dashboard"""").Range(""""$C$1"""""))
& ".xls"

 

[Clif McIrvin]

It must be too early in the morning .... not enough coffee, or something


Ignore my post .... what GS wrote originally was correct.

--
Clif

Don formulated the question :

[ ]
Garry,

I appreciate the assistance, unfortunately I am getting a VB error

Here is exactly the code I am using (the line above, the relevant
line, and the line below.

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\" &
"Plant_Premium_Heat_Map_WK" & CStr(Sheets("Dashboard").Range("$C$1"))
& ".xls"
ActiveWindow.Close

--------

Try
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\" &
"Plant_Premium_Heat_Map_WK" & _
CStr(Sheets(""""Dashboard"""").Range(""""$C$1"""""))
& ".xls"

 

[Don]

news:8c09988b-1f1f-4b55-98bd-7b6531cdbebf@e21g2000vbz.googlegroups.com....

[ ]
Cliff,

I also tried you solution and got an error as well on the 6th line
shown below   strFiscalWeek = Format(Range(cstrFiscalWeekAddr), "0")
------------

I did note that this was air code ... meaning you need to do testing.

Assumptions:
   Dashboard!C1 is a legal address from the code that is running.
   The contents of C1 is a number.

Just telling us you get an error is not enough information for us to
help you further ... at the least provide the actual text of the error
message .... better yet, use the VBE debugger and report the contents of
the relevant variables.

From the debugger window go to the immediate window (ctrl+g) and type
?cstrFiscalWeekAddr
?Range(cstrFiscalWeekAddr)

and see what is printed.  If that doesn't get you going, post back with
the actual error message(s) and the results of the immediate print
statements above.

--
Clif

    Application.DisplayAlerts = False
        Const cstrFiscalWeekAddr As String = "Dashboard!C1"
        Dim strFiscalWeek As Intr

        strFiscalWeek = Cint(Format(Range(cstrFiscalWeekAddr), "0"))
        strFilename = _
"P:\Finance\central\Finance\Daily\Plant_Premium_Heat_Map_WK" _
   & strFiscalWeek & ".xls"

 ActiveWorkbook.SaveAs Filename:=strFilename _
 , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
 ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.Close

Cliff,

I truly appreciate the assistance.

It finally dawned on me as I was working through it that Dashboard!C1
is not in the file I was working in but the parent workbook. I
activated the parent workbook prior to constructing the string then
reactivated the Plant Premium Workbook prior to saving and it worked.

The last thing I have to change as I move from week to week is to go
back and updaqte that same Fiscal Week indicator.

I thought this would work but I am missing something.

Sheets("Dashboard").Select
Const cstrFiscalWeek As String = "Dashboard!C1"
Dim strFiscalWeek As Intr

Range("C1").Select
ActiveCell.FormulaR1C1 = CInt(FiscalWeek) + 1

 

[Don]

"Don" <[email protected]> wrote in message

[ ]
Cliff,

I also tried you solution and got an error as well on the 6th line
shown below   strFiscalWeek = Format(Range(cstrFiscalWeekAddr), "0")
------------

I did note that this was air code ... meaning you need to do testing..

Assumptions:
   Dashboard!C1 is a legal address from the code that is running.
   The contents of C1 is a number.

Just telling us you get an error is not enough information for us to
help you further ... at the least provide the actual text of the error
message .... better yet, use the VBE debugger and report the contents of
the relevant variables.

From the debugger window go to the immediate window (ctrl+g) and type
?cstrFiscalWeekAddr
?Range(cstrFiscalWeekAddr)

and see what is printed.  If that doesn't get you going, post back with
the actual error message(s) and the results of the immediate print
statements above.
    Application.DisplayAlerts = False
        Const cstrFiscalWeekAddr As String = "Dashboard!C1"
        Dim strFiscalWeek As Intr

        strFiscalWeek = Cint(Format(Range(cstrFiscalWeekAddr), "0"))
        strFilename = _
"P:\Finance\central\Finance\Daily\Plant_Premium_Heat_Map_WK" _
   & strFiscalWeek & ".xls"

 ActiveWorkbook.SaveAs Filename:=strFilename _
 , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
 ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.Close

(clare reads his mail with moe, nomail feeds the bit bucket

Cliff,

I truly appreciate the assistance.

It finally dawned on me as I was working through it that Dashboard!C1
is not in the file I was working in but the parent workbook.  I
activated the parent workbook prior to constructing the string then
reactivated the Plant Premium Workbook prior to saving and it worked.

The last thing I have to change as I move from week to week is to go
back and updaqte that same Fiscal Week indicator.

I thought this would work but I am missing something.

    Sheets("Dashboard").Select
    Const cstrFiscalWeek As String = "Dashboard!C1"
    Dim strFiscalWeek As Intr

    Range("C1").Select
    ActiveCell.FormulaR1C1 = CInt(FiscalWeek) + 1- Hide quoted text-

- Show quoted text -

I also tried

Sheets("Dashboard").Select
Range("C1").Select

Const cstrFiscalWeek As String = "(Dashboard!C1)+1"

Range("C1").Select
ActiveCell.FormulaR1C1 = CInt(FiscalWeek)

which did not error out but it made C1 a zero instead of the 35 it
should be.....?

 

[Clif McIrvin]

[ ]

I truly appreciate the assistance.

You're welcome.

It finally dawned on me as I was working through it that Dashboard!C1
is not in the file I was working in but the parent workbook. I
activated the parent workbook prior to constructing the string then
reactivated the Plant Premium Workbook prior to saving and it worked.

I suspected something like that. Rather than activating the different
workbooks (that's a user interface activity, and 1) isn't necessary for
macro code and 2) is really slow from the viewpoint of macro code)
simply refer directly to the parent workbook. Something like:

lngFiscalWeek = Workbooks("parent
workbook").Sheets("Dashboard").Range("C1")

The last thing I have to change as I move from week to week is to go
back and updaqte that same Fiscal Week indicator.

I thought this would work but I am missing something.

Sheets("Dashboard").Select
Const cstrFiscalWeek As String = "Dashboard!C1"

Dim strFiscalWeek As Intr

Looks like something got dropped in the line above ... I don't recognize
" Intr " as a valid datatype.

A word about the variable names I used .... I've learned a bit about
using naming conventions, and even less about specific, commonly used
naming conventions. The general idea is to prefix your variable names
with codes that identify datatype and sometimes purpose -- thus

str --> string
cstr --> Constant of type string
lng --> long
var --> variant

so --

Dim strFiscalWeek As Integer

would be mis-labelled. You should use either

Dim intFiscalWeek as Integer - or -
Dim lngFiscalWeek as Long

I read many comments that there is no practical advantage to using the
shorter integer datatype, and with the large number of cells possible in
current versions of Excel there is some risk of code that uses integer
types breaking in 2007 / 2010 workbooks - so I'd recommend using the
long datatype even though you will "never" see a value larger than 52 in
this case.

Range("C1").Select
ActiveCell.FormulaR1C1 = CInt(FiscalWeek) + 1

I also tried

Const cstrFiscalWeek As String = "(Dashboard!C1)+1"

"Dashboard!C1" is a valid range address that Excel understands.
"(Dashboard!C1)+1" is nonsense (to Excel).

Range("C1").Select
ActiveCell.FormulaR1C1 = CInt(FiscalWeek)

which did not error out but it made C1 a zero instead of the 35 it
should be.....?

The obvious problem is I don't see that you assigned a value to the
variable FiscalWeek, so it will have a value of zero.

Try this version:

Sub Sample2()

Dim strFiscalWeek As String
Dim strFilename As String
Dim cellFiscalWeek As Range
Dim lngFiscalWeek As Long

' create a reference to the Fiscal Week number cell
' use the actual name of your parent workbook
Set cellFiscalWeek = _
Workbooks("parent workbook").Sheets("Dashboard").Range("C1")

lngFiscalWeek = cellFiscalWeek.Value
strFiscalWeek = Format(lngFiscalWeek, "0")
strFilename = _
"P:\Finance\central\Finance\Daily\Report_WK" _
& strFiscalWeek & ".xls"

ActiveWorkbook.SaveAs Filename:=strFilename _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

' update the fiscal week number cell
cellFiscalWeek.Value = lngFiscalWeek + 1

End Sub

 

[Don]

[ ]

I truly appreciate the assistance.

You're welcome.

It finally dawned on me as I was working through it that Dashboard!C1
is not in the file I was working in but the parent workbook. I
activated the parent workbook prior to constructing the string then
reactivated the Plant Premium Workbook prior to saving and it worked.

I suspected something like that.  Rather than activating the different
workbooks (that's a user interface activity, and 1) isn't necessary for
macro code and 2) is really slow from the viewpoint of macro code)
simply refer directly to the parent workbook.  Something like:

    lngFiscalWeek = Workbooks("parent
workbook").Sheets("Dashboard").Range("C1")

The last thing I have to change as I move from week to week is to go
back and updaqte that same Fiscal Week indicator.

I thought this would work but I am missing something.

Sheets("Dashboard").Select
Const cstrFiscalWeek As String = "Dashboard!C1"
Dim strFiscalWeek As Intr

Looks like something got dropped in the line above ... I don't recognize
" Intr " as a valid datatype.

A word about the variable names I used .... I've learned a bit about
using naming conventions, and even less about specific, commonly used
naming conventions.  The general idea is to prefix your variable names
with codes that identify datatype and sometimes purpose -- thus

str --> string
cstr --> Constant of type string
lng --> long
var --> variant

so --

Dim strFiscalWeek As Integer

would be mis-labelled.  You should use either

    Dim intFiscalWeek as Integer  - or -
    Dim lngFiscalWeek as Long

I read many comments that there is no practical advantage to using the
shorter integer datatype, and with the large number of cells possible in
current versions of Excel there is some risk of code that uses integer
types breaking in 2007 / 2010 workbooks - so I'd recommend using the
long datatype even though you will "never" see a value larger than 52 in
this case.

Range("C1").Select
ActiveCell.FormulaR1C1 = CInt(FiscalWeek) + 1
I also tried
   Sheets("Dashboard").Select
   Range("C1").Select
   Const cstrFiscalWeek As String = "(Dashboard!C1)+1"

"Dashboard!C1" is a valid range address that Excel understands.
"(Dashboard!C1)+1" is nonsense (to Excel).

   Range("C1").Select
   ActiveCell.FormulaR1C1 = CInt(FiscalWeek)
which did not error out but it made C1 a zero instead of the 35 it
should be.....?

The obvious problem is I don't see that you assigned a value to the
variable FiscalWeek, so it will have a value of zero.

Try this version:

Sub Sample2()

Dim strFiscalWeek As String
Dim strFilename As String
Dim cellFiscalWeek As Range
Dim lngFiscalWeek As Long

' create a reference to the Fiscal Week number cell
' use the actual name of your parent workbook
Set cellFiscalWeek = _
Workbooks("parent workbook").Sheets("Dashboard").Range("C1")

lngFiscalWeek = cellFiscalWeek.Value
strFiscalWeek = Format(lngFiscalWeek, "0")
strFilename = _
   "P:\Finance\central\Finance\Daily\Report_WK" _
   & strFiscalWeek & ".xls"

 ActiveWorkbook.SaveAs Filename:=strFilename _
 , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
 ReadOnlyRecommended:=False, CreateBackup:=False

' update the fiscal week number cell
cellFiscalWeek.Value = lngFiscalWeek + 1

End Sub

Cliff,

Thank you so much.

Don't you just hate spoon feeding self taught people like me?

Anyway, thanks so much for your assistance and guidance.

 

[Clif McIrvin]

[ ]

Cliff,

Thank you so much.

Don't you just hate spoon feeding self taught people like me?

Anyway, thanks so much for your assistance and guidance.

-------

You're very welcome. The tricky part (at least for me) is figuring out
what level of knowledge the OP actually has. I like to try and avoid
going over basics that are already known .... and I suspect more often
than not that I assume too much prior knowledge.

What's really great about these newsgroups is there's lots of really
knowledgeable people here who are willing to bring the rest of us along
.... and every now and then some of us self-taught folk see a chance to
chime in and give some of that help back.

Thanks for feeding back!

 

[GS]

Clif McIrvin wrote :

[ ]

Cliff,

Thank you so much.

Don't you just hate spoon feeding self taught people like me?

Anyway, thanks so much for your assistance and guidance.

-------

You're very welcome. The tricky part (at least for me) is figuring out what
level of knowledge the OP actually has. I like to try and avoid going over
basics that are already known .... and I suspect more often than not that I
assume too much prior knowledge.

What's really great about these newsgroups is there's lots of really
knowledgeable people here who are willing to bring the rest of us along ...
and every now and then some of us self-taught folk see a chance to chime in
and give some of that help back.

Thanks for feeding back!

Amen to that!