Variable holding across multiple subs?

M

Mark

I currently have this line in the first of a series of subs.

Z = ActiveWorkbook.Name

This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.

However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"

Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?
 
J

Jim Thomlinson

Without being able to see your code and how you are passing and using your
variables it is difficult to respond directly to your question. In general
though this is how it works... Variables die with the procedure that creates
them. As soon as you hit end sub or end function the declared variables are
premanenetly destroyed. There are two ways around this. One is to declare the
variable not withing the preocedure but rather at the beginning of the
module. This variable lasts for the duration of the code execution. If it is
declared Private then it can be used by any procedure within the module. If
it is declared public then it can be used by an procedure anywhere.

public myGlobalVariable as string
private myModuleVariable as string

These kind of declarations sound really great but here is the downside...
They are a beast to debug. This is becuase what procedure had them last and
what is the current value. At any given time it can be very difficult to
know. Use these as sparingly as possible!

The other way to keep a varible from being destroued is to declare it
"Static". Whe this happens the variable persists even after the procedure
ends. If you call the procedure again then the variable is just how you left
it.

Chances are for what you want you will probably end up usisng a global
variable. If all you are doing is storing the name of the current worksheet
and are not changing it back and forth throughout the code executions this is
probably reasonable...

HTH
 
T

Tushar Mehta

Pass Z as an argument to the subsequent subs as in:

sub Main()
Z=ActiveWorkbook.Name
'...
sub2 Z
'...
end sub
sub Sub2(Z as string)
'...
Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z _
& "]Plan'!R2170C242:R2229C248,7,FALSE)"
'...
end sub

Also, note that is is almost never necessary to select/activate
worksheets/ranges. For some more on that, see
Beyond Excel's recorder
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
M

Mark

When I tried this method, I received a number of errors saying that arguments
in all the subs within the module were not optional... so I made the argument
for all subs (z as string)... then I received an error for the calling of the
main sub for the button.

Tushar Mehta said:
Pass Z as an argument to the subsequent subs as in:

sub Main()
Z=ActiveWorkbook.Name
'...
sub2 Z
'...
end sub
sub Sub2(Z as string)
'...
Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z _
& "]Plan'!R2170C242:R2229C248,7,FALSE)"
'...
end sub

Also, note that is is almost never necessary to select/activate
worksheets/ranges. For some more on that, see
Beyond Excel's recorder
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I currently have this line in the first of a series of subs.

Z = ActiveWorkbook.Name

This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.

However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"

Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?
Click to expand...
Click to expand...
 
M

Mark

The variable, Z, needs to be the name of the worksheet from which the macro
is run.

The macro runs a number of subs that first create a new sheet. Then, the
macro pulls data from the original sheet to the new sheet. Currently, I use
the actual name of the original file -- but I want the variable to be used so
that I can set it to the workbook name, no matter how the file is renamed, so
it can be called throughout the subs.

I don't know much about 'passing' variables, etc. Not sure what that entails.



Jim Thomlinson said:
Without being able to see your code and how you are passing and using your
variables it is difficult to respond directly to your question. In general
though this is how it works... Variables die with the procedure that creates
them. As soon as you hit end sub or end function the declared variables are
premanenetly destroyed. There are two ways around this. One is to declare the
variable not withing the preocedure but rather at the beginning of the
module. This variable lasts for the duration of the code execution. If it is
declared Private then it can be used by any procedure within the module. If
it is declared public then it can be used by an procedure anywhere.

public myGlobalVariable as string
private myModuleVariable as string

These kind of declarations sound really great but here is the downside...
They are a beast to debug. This is becuase what procedure had them last and
what is the current value. At any given time it can be very difficult to
know. Use these as sparingly as possible!

The other way to keep a varible from being destroued is to declare it
"Static". Whe this happens the variable persists even after the procedure
ends. If you call the procedure again then the variable is just how you left
it.

Chances are for what you want you will probably end up usisng a global
variable. If all you are doing is storing the name of the current worksheet
and are not changing it back and forth throughout the code executions this is
probably reasonable...

HTH


Mark said:
I currently have this line in the first of a series of subs.

Z = ActiveWorkbook.Name

This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.

However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"

Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?
Click to expand...
Click to expand...
 
J

Jim Thomlinson

If I understand you correctly. The macro is desinged to be run against any
sheet in the workbook. When it is runs it creates a new sheet (in the same
workbook or in a whole new workbook?) and copies some info from the original
sheet to that new sheet that you just created? There are some fairly easy
ways to do this and I am a little worried that your code is getting more
complicated that it needs to be. Post your code and lets take a look at what
you have so far.

Mark said:
The variable, Z, needs to be the name of the worksheet from which the macro
is run.

The macro runs a number of subs that first create a new sheet. Then, the
macro pulls data from the original sheet to the new sheet. Currently, I use
the actual name of the original file -- but I want the variable to be used so
that I can set it to the workbook name, no matter how the file is renamed, so
it can be called throughout the subs.

I don't know much about 'passing' variables, etc. Not sure what that entails.



Jim Thomlinson said:
Without being able to see your code and how you are passing and using your
variables it is difficult to respond directly to your question. In general
though this is how it works... Variables die with the procedure that creates
them. As soon as you hit end sub or end function the declared variables are
premanenetly destroyed. There are two ways around this. One is to declare the
variable not withing the preocedure but rather at the beginning of the
module. This variable lasts for the duration of the code execution. If it is
declared Private then it can be used by any procedure within the module. If
it is declared public then it can be used by an procedure anywhere.

public myGlobalVariable as string
private myModuleVariable as string

These kind of declarations sound really great but here is the downside...
They are a beast to debug. This is becuase what procedure had them last and
what is the current value. At any given time it can be very difficult to
know. Use these as sparingly as possible!

The other way to keep a varible from being destroued is to declare it
"Static". Whe this happens the variable persists even after the procedure
ends. If you call the procedure again then the variable is just how you left
it.

Chances are for what you want you will probably end up usisng a global
variable. If all you are doing is storing the name of the current worksheet
and are not changing it back and forth throughout the code executions this is
probably reasonable...

HTH


Mark said:
I currently have this line in the first of a series of subs.

Z = ActiveWorkbook.Name

This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.

However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"

Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?
Click to expand...
Click to expand...
Click to expand...
 
M

Mark

The hope is to pull data from one workbook to a newly create workbook. Here
is the code from the module to create the report. There's a lot, mostly
formatting.



Sub Report_Save_Check1()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Z = ActiveWorkbook.Name

Msg = "If you have not completed your estimates, this report will be
incomplete or empty. Do you wish to continue?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Project Estimating Model" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Call Cost_Report_Save
Else
End

End If
End Sub


Sub Cost_Report_Save()

Dim SaveName As Variant
Dim fFilter As String
Dim NewName As String

Dim wbk As Workbook

Set wbk = Workbooks.Add

run_date = Format(Date, "dd-mmm-yy")
project_name = Application.ActiveWorkbook.Name

MsgBox "You have chosen to create a cost report. This report will be
saved in a new workbook."
NewName = "Estimated Cost Report_" & run_date

fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

If SaveName = False Then
'use cancelled--what to do?
Else
ThisWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlWorkbookNormal
End If

MsgBox "Excel will now generate the cost report. This process may take
up to 1 minute. Click 'Ok' to continue."

Call Populate

End Sub

Sub Tech_Rates()

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'["&Z&"]Plan'!R2170C242:R2229C248,7,FALSE)"

Range("AM64").Select
Selection.AutoFill Destination:=Range("AM64:AM123"),
Type:=xlFillValues
Range("AM64:AM123").Select



End Sub
Sub Tech_Names()


Range("A64").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[440]C20"

Range("A64").Select
Selection.AutoFill Destination:=Range("A64:A123"),
Type:=xlFillValues
Range("A64:A123").Select


End Sub
Sub Tech_Data()


ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & Z & "]Summary Data'!R504C20:R923C20,RC1,'[" & Z &
"]Summary Data'!R504C34:R923C34)*RC39"

Range("B64").Select
Selection.AutoFill Destination:=Range("B64:AK64"),
Type:=xlFillValues
Range("B64:AK64").Select
Selection.AutoFill Destination:=Range("B64:AK123"),
Type:=xlFillValues
Range("B64:AK123").Select


Range("B64").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & Z & "]Summary Data'!R504C20:R923C20,RC1,'[" &
Z & "]Summary Data'!R504C[32]:R923C[32])*RC39"

Range("B64").Select
Selection.AutoFill Destination:=Range("B64:AK64"),
Type:=xlFillValues
Range("B64:AK64").Select
Selection.AutoFill Destination:=Range("B64:AK123"),
Type:=xlFillValues
Range("B64:AK123").Select


End Sub

Sub Final_Format()

Dim i As Long

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit


Range("B3:AK125").Activate
Selection.Style = "Currency"
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($*
""-""??_);_(@_)"

Range("B2:AK2").Select
Selection.NumberFormat = "[$-409]mmm-yy;@"


Sheets("sheet1").Select
Range("al3:al125").Select
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i) <> 0 Then
Selection.Rows(i).EntireRow.Hidden = False
End If
Next i


Sheets("sheet1").Select
Range("al3:al125").Select
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i) = 0 Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i

Sheets("sheet1").Select
Range("al:am").Select
Selection.EntireColumn.Hidden = True


End Sub
Sub Business_Names()

Range("a3").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[4]C51"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A62"),
Type:=xlFillValues
Range("A3:A62").Select


End Sub
Sub Dates()

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R503C[32]"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:AK2"),
Type:=xlFillDefault
Range("B2:AK2").Select
Selection.Font.Bold = True

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub

Sub Business_data()

Range("B3").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[79]C[58]"

Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B62"), Type:=xlFillValues
Range("B3:B62").Select
Selection.AutoFill Destination:=Range("B3:AK62"), Type:=xlFillValues
Range("B3:AK62").Select

End Sub
Sub Title()

Selection.RowHeight = 42.75
Range("A1").Select
ActiveCell.FormulaR1C1 = "Project Cost Estimates by Month"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Range("E1").Select
Rows("1:1").EntireRow.AutoFit
End Sub
Sub Report_Format()
'
Range("B2:AK2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A3:A62").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B63:AK63").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 2
ActiveWindow.SmallScroll Down:=32
Range("A64:A123").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 45
ActiveWindow.SmallScroll Down:=7
Range("B124:AK125").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("A64:A123").Select
Range("A123").Activate
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A124:A125").Select
Range("A125").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("B63:AK63,B124:AK124").Select
Range("B124").Activate
With Selection.Interior
.ColorIndex = 20
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A63,A124").Select
Range("A124").Activate
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A125:AK125").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A63").Select
ActiveCell.FormulaR1C1 = "Business Total"
Range("A124").Select
ActiveCell.FormulaR1C1 = "Technology Total"
Range("A125").Select
ActiveCell.FormulaR1C1 = "Overall Total"
Range("B64:AK123").Select
Range("AK123").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("L101").Select

Range("B4:C7").Select
Selection.Cut Destination:=Range("D5:E8")
Range("B3:AK62").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells.Select
Cells.EntireColumn.AutoFit
Selection.RowHeight = 7.5
Cells.EntireRow.AutoFit
Range("F11").Select

Range("O60").Select

End Sub
Sub Report_Calculations()
'

Range("B63").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-60]C:R[-1]C)"
Range("B63").Select
Selection.AutoFill Destination:=Range("B63:AK63"), Type:=xlFillValues
Range("B63:AK63").Select

Range("B124").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-60]C:R[-1]C)"
Range("B124").Select
Selection.AutoFill Destination:=Range("B124:AK124"),
Type:=xlFillValues
Range("B124:AK124").Select

Range("B125").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C,R[-62]C)"
Range("B125").Select
Selection.AutoFill Destination:=Range("B125:AK125"),
Type:=xlFillValues
Range("B125:AK125").Select

Range("AL3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-36]:RC[-1])"
Range("AL3").Select
Selection.AutoFill Destination:=Range("AL3:AL125"), Type:=xlFillValues
Range("AL3:AL125").Select

Columns("AL:AL").Select
Range("AL3").Activate
Selection.EntireColumn.Hidden = True
End Sub

Sub Populate()

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

Call Report_Format
Call Dates
Call Report_Calculations
Call Tech_Data
Call Tech_Rates
Call Tech_Names
Call Business_Names
Call Business_data
Call Title

Calculate


Call Final_Format


.ScreenUpdating = True

Range("a1").Select


End With
End Sub




Jim Thomlinson said:
If I understand you correctly. The macro is desinged to be run against any
sheet in the workbook. When it is runs it creates a new sheet (in the same
workbook or in a whole new workbook?) and copies some info from the original
sheet to that new sheet that you just created? There are some fairly easy
ways to do this and I am a little worried that your code is getting more
complicated that it needs to be. Post your code and lets take a look at what
you have so far.

Mark said:
The variable, Z, needs to be the name of the worksheet from which the macro
is run.

The macro runs a number of subs that first create a new sheet. Then, the
macro pulls data from the original sheet to the new sheet. Currently, I use
the actual name of the original file -- but I want the variable to be used so
that I can set it to the workbook name, no matter how the file is renamed, so
it can be called throughout the subs.

I don't know much about 'passing' variables, etc. Not sure what that entails.



Jim Thomlinson said:
Without being able to see your code and how you are passing and using your
variables it is difficult to respond directly to your question. In general
though this is how it works... Variables die with the procedure that creates
them. As soon as you hit end sub or end function the declared variables are
premanenetly destroyed. There are two ways around this. One is to declare the
variable not withing the preocedure but rather at the beginning of the
module. This variable lasts for the duration of the code execution. If it is
declared Private then it can be used by any procedure within the module. If
it is declared public then it can be used by an procedure anywhere.

public myGlobalVariable as string
private myModuleVariable as string

These kind of declarations sound really great but here is the downside...
They are a beast to debug. This is becuase what procedure had them last and
what is the current value. At any given time it can be very difficult to
know. Use these as sparingly as possible!

The other way to keep a varible from being destroued is to declare it
"Static". Whe this happens the variable persists even after the procedure
ends. If you call the procedure again then the variable is just how you left
it.

Chances are for what you want you will probably end up usisng a global
variable. If all you are doing is storing the name of the current worksheet
and are not changing it back and forth throughout the code executions this is
probably reasonable...

HTH


:

I currently have this line in the first of a series of subs.

Z = ActiveWorkbook.Name

This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.

However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"

Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?
Click to expand...
Click to expand...
Click to expand...
 
J

Jim Thomlinson

At the very top of the code module I want you to add in two lines...

Option Explicit
Private Z as String

Now click on Debug and Compile...

You are going to get a bunch of error messages where you have not declared
variables you are using. Where you get those errors declare the variables
with a dim statement at the beginning of the procedure.

That is a good place to start... It should clean up a lot of the problems...


Mark said:
The hope is to pull data from one workbook to a newly create workbook. Here
is the code from the module to create the report. There's a lot, mostly
formatting.



Sub Report_Save_Check1()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Z = ActiveWorkbook.Name

Msg = "If you have not completed your estimates, this report will be
incomplete or empty. Do you wish to continue?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Project Estimating Model" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Call Cost_Report_Save
Else
End

End If
End Sub


Sub Cost_Report_Save()

Dim SaveName As Variant
Dim fFilter As String
Dim NewName As String

Dim wbk As Workbook

Set wbk = Workbooks.Add

run_date = Format(Date, "dd-mmm-yy")
project_name = Application.ActiveWorkbook.Name

MsgBox "You have chosen to create a cost report. This report will be
saved in a new workbook."
NewName = "Estimated Cost Report_" & run_date

fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

If SaveName = False Then
'use cancelled--what to do?
Else
ThisWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlWorkbookNormal
End If

MsgBox "Excel will now generate the cost report. This process may take
up to 1 minute. Click 'Ok' to continue."

Call Populate

End Sub

Sub Tech_Rates()

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'["&Z&"]Plan'!R2170C242:R2229C248,7,FALSE)"

Range("AM64").Select
Selection.AutoFill Destination:=Range("AM64:AM123"),
Type:=xlFillValues
Range("AM64:AM123").Select



End Sub
Sub Tech_Names()


Range("A64").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[440]C20"

Range("A64").Select
Selection.AutoFill Destination:=Range("A64:A123"),
Type:=xlFillValues
Range("A64:A123").Select


End Sub
Sub Tech_Data()


ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & Z & "]Summary Data'!R504C20:R923C20,RC1,'[" & Z &
"]Summary Data'!R504C34:R923C34)*RC39"

Range("B64").Select
Selection.AutoFill Destination:=Range("B64:AK64"),
Type:=xlFillValues
Range("B64:AK64").Select
Selection.AutoFill Destination:=Range("B64:AK123"),
Type:=xlFillValues
Range("B64:AK123").Select


Range("B64").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & Z & "]Summary Data'!R504C20:R923C20,RC1,'[" &
Z & "]Summary Data'!R504C[32]:R923C[32])*RC39"

Range("B64").Select
Selection.AutoFill Destination:=Range("B64:AK64"),
Type:=xlFillValues
Range("B64:AK64").Select
Selection.AutoFill Destination:=Range("B64:AK123"),
Type:=xlFillValues
Range("B64:AK123").Select


End Sub

Sub Final_Format()

Dim i As Long

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit


Range("B3:AK125").Activate
Selection.Style = "Currency"
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($*
""-""??_);_(@_)"

Range("B2:AK2").Select
Selection.NumberFormat = "[$-409]mmm-yy;@"


Sheets("sheet1").Select
Range("al3:al125").Select
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i) <> 0 Then
Selection.Rows(i).EntireRow.Hidden = False
End If
Next i


Sheets("sheet1").Select
Range("al3:al125").Select
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i) = 0 Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i

Sheets("sheet1").Select
Range("al:am").Select
Selection.EntireColumn.Hidden = True


End Sub
Sub Business_Names()

Range("a3").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[4]C51"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A62"),
Type:=xlFillValues
Range("A3:A62").Select


End Sub
Sub Dates()

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R503C[32]"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:AK2"),
Type:=xlFillDefault
Range("B2:AK2").Select
Selection.Font.Bold = True

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub

Sub Business_data()

Range("B3").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[79]C[58]"

Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B62"), Type:=xlFillValues
Range("B3:B62").Select
Selection.AutoFill Destination:=Range("B3:AK62"), Type:=xlFillValues
Range("B3:AK62").Select

End Sub
Sub Title()

Selection.RowHeight = 42.75
Range("A1").Select
ActiveCell.FormulaR1C1 = "Project Cost Estimates by Month"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Range("E1").Select
Rows("1:1").EntireRow.AutoFit
End Sub
Sub Report_Format()
'
Range("B2:AK2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A3:A62").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B63:AK63").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 2
ActiveWindow.SmallScroll Down:=32
Range("A64:A123").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 45
ActiveWindow.SmallScroll Down:=7
Range("B124:AK125").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("A64:A123").Select
Range("A123").Activate
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A124:A125").Select
Range("A125").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("B63:AK63,B124:AK124").Select
Range("B124").Activate
With Selection.Interior
.ColorIndex = 20
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A63,A124").Select
Range("A124").Activate
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A125:AK125").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A63").Select
ActiveCell.FormulaR1C1 = "Business Total"
Range("A124").Select
ActiveCell.FormulaR1C1 = "Technology Total"
Range("A125").Select
ActiveCell.FormulaR1C1 = "Overall Total"
Range("B64:AK123").Select
Range("AK123").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("L101").Select

Range("B4:C7").Select
Selection.Cut Destination:=Range("D5:E8")
Range("B3:AK62").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells.Select
Cells.EntireColumn.AutoFit
Selection.RowHeight = 7.5
Cells.EntireRow.AutoFit
Range("F11").Select

Range("O60").Select

End Sub
Sub Report_Calculations()
'

Range("B63").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-60]C:R[-1]C)"
Range("B63").Select
Selection.AutoFill Destination:=Range("B63:AK63"), Type:=xlFillValues
Range("B63:AK63").Select

Range("B124").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-60]C:R[-1]C)"
Range("B124").Select
Selection.AutoFill Destination:=Range("B124:AK124"),
Type:=xlFillValues
Range("B124:AK124").Select

Range("B125").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C,R[-62]C)"
Range("B125").Select
Selection.AutoFill Destination:=Range("B125:AK125"),
Type:=xlFillValues
Range("B125:AK125").Select

Range("AL3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-36]:RC[-1])"
Range("AL3").Select
Selection.AutoFill Destination:=Range("AL3:AL125"), Type:=xlFillValues
Range("AL3:AL125").Select

Columns("AL:AL").Select
Range("AL3").Activate
Selection.EntireColumn.Hidden = True
End Sub

Sub Populate()

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

Call Report_Format
Call Dates
Call Report_Calculations
Call Tech_Data
Call Tech_Rates
Call Tech_Names
Call Business_Names
Call Business_data
Call Title

Calculate


Call Final_Format


.ScreenUpdating = True

Range("a1").Select


End With
End Sub




Jim Thomlinson said:
If I understand you correctly. The macro is desinged to be run against any
sheet in the workbook. When it is runs it creates a new sheet (in the same
workbook or in a whole new workbook?) and copies some info from the original
sheet to that new sheet that you just created? There are some fairly easy
ways to do this and I am a little worried that your code is getting more
complicated that it needs to be. Post your code and lets take a look at what
you have so far.

Mark said:
The variable, Z, needs to be the name of the worksheet from which the macro
is run.

The macro runs a number of subs that first create a new sheet. Then, the
macro pulls data from the original sheet to the new sheet. Currently, I use
the actual name of the original file -- but I want the variable to be used so
that I can set it to the workbook name, no matter how the file is renamed, so
it can be called throughout the subs.

I don't know much about 'passing' variables, etc. Not sure what that entails.



:

Without being able to see your code and how you are passing and using your
variables it is difficult to respond directly to your question. In general
though this is how it works... Variables die with the procedure that creates
them. As soon as you hit end sub or end function the declared variables are
premanenetly destroyed. There are two ways around this. One is to declare the
variable not withing the preocedure but rather at the beginning of the
module. This variable lasts for the duration of the code execution. If it is
declared Private then it can be used by any procedure within the module. If
it is declared public then it can be used by an procedure anywhere.

public myGlobalVariable as string
private myModuleVariable as string

These kind of declarations sound really great but here is the downside...
They are a beast to debug. This is becuase what procedure had them last and
what is the current value. At any given time it can be very difficult to
know. Use these as sparingly as possible!

The other way to keep a varible from being destroued is to declare it
"Static". Whe this happens the variable persists even after the procedure
ends. If you call the procedure again then the variable is just how you left
it.

Chances are for what you want you will probably end up usisng a global
variable. If all you are doing is storing the name of the current worksheet
and are not changing it back and forth throughout the code executions this is
probably reasonable...

HTH


:

I currently have this line in the first of a series of subs.

Z = ActiveWorkbook.Name

This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.

However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"

Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?
Click to expand...
Click to expand...
Click to expand...
 
M

Mark

That did it -- it only had problems with a few un-dim'd variables. Slow as
anything, but it created the report.

Jim Thomlinson said:
At the very top of the code module I want you to add in two lines...

Option Explicit
Private Z as String

Now click on Debug and Compile...

You are going to get a bunch of error messages where you have not declared
variables you are using. Where you get those errors declare the variables
with a dim statement at the beginning of the procedure.

That is a good place to start... It should clean up a lot of the problems...


Mark said:
The hope is to pull data from one workbook to a newly create workbook. Here
is the code from the module to create the report. There's a lot, mostly
formatting.



Sub Report_Save_Check1()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Z = ActiveWorkbook.Name

Msg = "If you have not completed your estimates, this report will be
incomplete or empty. Do you wish to continue?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Project Estimating Model" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Call Cost_Report_Save
Else
End

End If
End Sub


Sub Cost_Report_Save()

Dim SaveName As Variant
Dim fFilter As String
Dim NewName As String

Dim wbk As Workbook

Set wbk = Workbooks.Add

run_date = Format(Date, "dd-mmm-yy")
project_name = Application.ActiveWorkbook.Name

MsgBox "You have chosen to create a cost report. This report will be
saved in a new workbook."
NewName = "Estimated Cost Report_" & run_date

fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

If SaveName = False Then
'use cancelled--what to do?
Else
ThisWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlWorkbookNormal
End If

MsgBox "Excel will now generate the cost report. This process may take
up to 1 minute. Click 'Ok' to continue."

Call Populate

End Sub

Sub Tech_Rates()

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'["&Z&"]Plan'!R2170C242:R2229C248,7,FALSE)"

Range("AM64").Select
Selection.AutoFill Destination:=Range("AM64:AM123"),
Type:=xlFillValues
Range("AM64:AM123").Select



End Sub
Sub Tech_Names()


Range("A64").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[440]C20"

Range("A64").Select
Selection.AutoFill Destination:=Range("A64:A123"),
Type:=xlFillValues
Range("A64:A123").Select


End Sub
Sub Tech_Data()


ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & Z & "]Summary Data'!R504C20:R923C20,RC1,'[" & Z &
"]Summary Data'!R504C34:R923C34)*RC39"

Range("B64").Select
Selection.AutoFill Destination:=Range("B64:AK64"),
Type:=xlFillValues
Range("B64:AK64").Select
Selection.AutoFill Destination:=Range("B64:AK123"),
Type:=xlFillValues
Range("B64:AK123").Select


Range("B64").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & Z & "]Summary Data'!R504C20:R923C20,RC1,'[" &
Z & "]Summary Data'!R504C[32]:R923C[32])*RC39"

Range("B64").Select
Selection.AutoFill Destination:=Range("B64:AK64"),
Type:=xlFillValues
Range("B64:AK64").Select
Selection.AutoFill Destination:=Range("B64:AK123"),
Type:=xlFillValues
Range("B64:AK123").Select


End Sub

Sub Final_Format()

Dim i As Long

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit


Range("B3:AK125").Activate
Selection.Style = "Currency"
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($*
""-""??_);_(@_)"

Range("B2:AK2").Select
Selection.NumberFormat = "[$-409]mmm-yy;@"


Sheets("sheet1").Select
Range("al3:al125").Select
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i) <> 0 Then
Selection.Rows(i).EntireRow.Hidden = False
End If
Next i


Sheets("sheet1").Select
Range("al3:al125").Select
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i) = 0 Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i

Sheets("sheet1").Select
Range("al:am").Select
Selection.EntireColumn.Hidden = True


End Sub
Sub Business_Names()

Range("a3").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[4]C51"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A62"),
Type:=xlFillValues
Range("A3:A62").Select


End Sub
Sub Dates()

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R503C[32]"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:AK2"),
Type:=xlFillDefault
Range("B2:AK2").Select
Selection.Font.Bold = True

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub

Sub Business_data()

Range("B3").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[79]C[58]"

Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B62"), Type:=xlFillValues
Range("B3:B62").Select
Selection.AutoFill Destination:=Range("B3:AK62"), Type:=xlFillValues
Range("B3:AK62").Select

End Sub
Sub Title()

Selection.RowHeight = 42.75
Range("A1").Select
ActiveCell.FormulaR1C1 = "Project Cost Estimates by Month"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Range("E1").Select
Rows("1:1").EntireRow.AutoFit
End Sub
Sub Report_Format()
'
Range("B2:AK2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A3:A62").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B63:AK63").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 2
ActiveWindow.SmallScroll Down:=32
Range("A64:A123").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 45
ActiveWindow.SmallScroll Down:=7
Range("B124:AK125").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("A64:A123").Select
Range("A123").Activate
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A124:A125").Select
Range("A125").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("B63:AK63,B124:AK124").Select
Range("B124").Activate
With Selection.Interior
.ColorIndex = 20
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A63,A124").Select
Range("A124").Activate
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A125:AK125").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A63").Select
ActiveCell.FormulaR1C1 = "Business Total"
Range("A124").Select
ActiveCell.FormulaR1C1 = "Technology Total"
Range("A125").Select
ActiveCell.FormulaR1C1 = "Overall Total"
Range("B64:AK123").Select
Range("AK123").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("L101").Select

Range("B4:C7").Select
Selection.Cut Destination:=Range("D5:E8")
Range("B3:AK62").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells.Select
Cells.EntireColumn.AutoFit
Selection.RowHeight = 7.5
Cells.EntireRow.AutoFit
Range("F11").Select

Range("O60").Select

End Sub
Sub Report_Calculations()
'

Range("B63").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-60]C:R[-1]C)"
Range("B63").Select
Selection.AutoFill Destination:=Range("B63:AK63"), Type:=xlFillValues
Range("B63:AK63").Select

Range("B124").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-60]C:R[-1]C)"
Range("B124").Select
Selection.AutoFill Destination:=Range("B124:AK124"),
Type:=xlFillValues
Range("B124:AK124").Select

Range("B125").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C,R[-62]C)"
Range("B125").Select
Selection.AutoFill Destination:=Range("B125:AK125"),
Type:=xlFillValues
Range("B125:AK125").Select

Range("AL3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-36]:RC[-1])"
Range("AL3").Select
Selection.AutoFill Destination:=Range("AL3:AL125"), Type:=xlFillValues
Range("AL3:AL125").Select

Columns("AL:AL").Select
Range("AL3").Activate
Selection.EntireColumn.Hidden = True
End Sub

Sub Populate()

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

Call Report_Format
Call Dates
Call Report_Calculations
Call Tech_Data
Call Tech_Rates
Call Tech_Names
Call Business_Names
Call Business_data
Call Title

Calculate


Call Final_Format


.ScreenUpdating = True

Range("a1").Select


End With
End Sub




Jim Thomlinson said:
If I understand you correctly. The macro is desinged to be run against any
sheet in the workbook. When it is runs it creates a new sheet (in the same
workbook or in a whole new workbook?) and copies some info from the original
sheet to that new sheet that you just created? There are some fairly easy
ways to do this and I am a little worried that your code is getting more
complicated that it needs to be. Post your code and lets take a look at what
you have so far.

:

The variable, Z, needs to be the name of the worksheet from which the macro
is run.

The macro runs a number of subs that first create a new sheet. Then, the
macro pulls data from the original sheet to the new sheet. Currently, I use
the actual name of the original file -- but I want the variable to be used so
that I can set it to the workbook name, no matter how the file is renamed, so
it can be called throughout the subs.

I don't know much about 'passing' variables, etc. Not sure what that entails.



:

Without being able to see your code and how you are passing and using your
variables it is difficult to respond directly to your question. In general
though this is how it works... Variables die with the procedure that creates
them. As soon as you hit end sub or end function the declared variables are
premanenetly destroyed. There are two ways around this. One is to declare the
variable not withing the preocedure but rather at the beginning of the
module. This variable lasts for the duration of the code execution. If it is
declared Private then it can be used by any procedure within the module. If
it is declared public then it can be used by an procedure anywhere.

public myGlobalVariable as string
private myModuleVariable as string

These kind of declarations sound really great but here is the downside...
They are a beast to debug. This is becuase what procedure had them last and
what is the current value. At any given time it can be very difficult to
know. Use these as sparingly as possible!

The other way to keep a varible from being destroued is to declare it
"Static". Whe this happens the variable persists even after the procedure
ends. If you call the procedure again then the variable is just how you left
it.

Chances are for what you want you will probably end up usisng a global
variable. If all you are doing is storing the name of the current worksheet
and are not changing it back and forth throughout the code executions this is
probably reasonable...

HTH


:

I currently have this line in the first of a series of subs.

Z = ActiveWorkbook.Name

This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.

However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"

Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?
Click to expand...
Click to expand...
Click to expand...
 
J

Jim Thomlinson

In your vb environment select tools Options and on the general tab there is a
unchecked option called Require variable declarations. Check this off. Now
when new modules are created they will default with Option Explicit at the
top. It will ensure that you always declare your variables which makeds
debugging a whole pile easier...

Mark said:
That did it -- it only had problems with a few un-dim'd variables. Slow as
anything, but it created the report.

Jim Thomlinson said:
At the very top of the code module I want you to add in two lines...

Option Explicit
Private Z as String

Now click on Debug and Compile...

You are going to get a bunch of error messages where you have not declared
variables you are using. Where you get those errors declare the variables
with a dim statement at the beginning of the procedure.

That is a good place to start... It should clean up a lot of the problems...


Mark said:
The hope is to pull data from one workbook to a newly create workbook. Here
is the code from the module to create the report. There's a lot, mostly
formatting.



Sub Report_Save_Check1()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Z = ActiveWorkbook.Name

Msg = "If you have not completed your estimates, this report will be
incomplete or empty. Do you wish to continue?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Project Estimating Model" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Call Cost_Report_Save
Else
End

End If
End Sub


Sub Cost_Report_Save()

Dim SaveName As Variant
Dim fFilter As String
Dim NewName As String

Dim wbk As Workbook

Set wbk = Workbooks.Add

run_date = Format(Date, "dd-mmm-yy")
project_name = Application.ActiveWorkbook.Name

MsgBox "You have chosen to create a cost report. This report will be
saved in a new workbook."
NewName = "Estimated Cost Report_" & run_date

fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

If SaveName = False Then
'use cancelled--what to do?
Else
ThisWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlWorkbookNormal
End If

MsgBox "Excel will now generate the cost report. This process may take
up to 1 minute. Click 'Ok' to continue."

Call Populate

End Sub

Sub Tech_Rates()

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'["&Z&"]Plan'!R2170C242:R2229C248,7,FALSE)"

Range("AM64").Select
Selection.AutoFill Destination:=Range("AM64:AM123"),
Type:=xlFillValues
Range("AM64:AM123").Select



End Sub
Sub Tech_Names()


Range("A64").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[440]C20"

Range("A64").Select
Selection.AutoFill Destination:=Range("A64:A123"),
Type:=xlFillValues
Range("A64:A123").Select


End Sub
Sub Tech_Data()


ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & Z & "]Summary Data'!R504C20:R923C20,RC1,'[" & Z &
"]Summary Data'!R504C34:R923C34)*RC39"

Range("B64").Select
Selection.AutoFill Destination:=Range("B64:AK64"),
Type:=xlFillValues
Range("B64:AK64").Select
Selection.AutoFill Destination:=Range("B64:AK123"),
Type:=xlFillValues
Range("B64:AK123").Select


Range("B64").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & Z & "]Summary Data'!R504C20:R923C20,RC1,'[" &
Z & "]Summary Data'!R504C[32]:R923C[32])*RC39"

Range("B64").Select
Selection.AutoFill Destination:=Range("B64:AK64"),
Type:=xlFillValues
Range("B64:AK64").Select
Selection.AutoFill Destination:=Range("B64:AK123"),
Type:=xlFillValues
Range("B64:AK123").Select


End Sub

Sub Final_Format()

Dim i As Long

Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit


Range("B3:AK125").Activate
Selection.Style = "Currency"
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($*
""-""??_);_(@_)"

Range("B2:AK2").Select
Selection.NumberFormat = "[$-409]mmm-yy;@"


Sheets("sheet1").Select
Range("al3:al125").Select
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i) <> 0 Then
Selection.Rows(i).EntireRow.Hidden = False
End If
Next i


Sheets("sheet1").Select
Range("al3:al125").Select
For i = Selection.Cells.Count To 1 Step -1
If Selection.Cells(i) = 0 Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i

Sheets("sheet1").Select
Range("al:am").Select
Selection.EntireColumn.Hidden = True


End Sub
Sub Business_Names()

Range("a3").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[4]C51"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A62"),
Type:=xlFillValues
Range("A3:A62").Select


End Sub
Sub Dates()

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R503C[32]"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:AK2"),
Type:=xlFillDefault
Range("B2:AK2").Select
Selection.Font.Bold = True

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub

Sub Business_data()

Range("B3").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R[79]C[58]"

Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B62"), Type:=xlFillValues
Range("B3:B62").Select
Selection.AutoFill Destination:=Range("B3:AK62"), Type:=xlFillValues
Range("B3:AK62").Select

End Sub
Sub Title()

Selection.RowHeight = 42.75
Range("A1").Select
ActiveCell.FormulaR1C1 = "Project Cost Estimates by Month"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Range("E1").Select
Rows("1:1").EntireRow.AutoFit
End Sub
Sub Report_Format()
'
Range("B2:AK2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A3:A62").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B63:AK63").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 2
ActiveWindow.SmallScroll Down:=32
Range("A64:A123").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 45
ActiveWindow.SmallScroll Down:=7
Range("B124:AK125").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("A64:A123").Select
Range("A123").Activate
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A124:A125").Select
Range("A125").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("B63:AK63,B124:AK124").Select
Range("B124").Activate
With Selection.Interior
.ColorIndex = 20
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A63,A124").Select
Range("A124").Activate
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A125:AK125").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A63").Select
ActiveCell.FormulaR1C1 = "Business Total"
Range("A124").Select
ActiveCell.FormulaR1C1 = "Technology Total"
Range("A125").Select
ActiveCell.FormulaR1C1 = "Overall Total"
Range("B64:AK123").Select
Range("AK123").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("L101").Select

Range("B4:C7").Select
Selection.Cut Destination:=Range("D5:E8")
Range("B3:AK62").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Cells.Select
Cells.EntireColumn.AutoFit
Selection.RowHeight = 7.5
Cells.EntireRow.AutoFit
Range("F11").Select

Range("O60").Select

End Sub
Sub Report_Calculations()
'

Range("B63").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-60]C:R[-1]C)"
Range("B63").Select
Selection.AutoFill Destination:=Range("B63:AK63"), Type:=xlFillValues
Range("B63:AK63").Select

Range("B124").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-60]C:R[-1]C)"
Range("B124").Select
Selection.AutoFill Destination:=Range("B124:AK124"),
Type:=xlFillValues
Range("B124:AK124").Select

Range("B125").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C,R[-62]C)"
Range("B125").Select
Selection.AutoFill Destination:=Range("B125:AK125"),
Type:=xlFillValues
Range("B125:AK125").Select

Range("AL3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-36]:RC[-1])"
Range("AL3").Select
Selection.AutoFill Destination:=Range("AL3:AL125"), Type:=xlFillValues
Range("AL3:AL125").Select

Columns("AL:AL").Select
Range("AL3").Activate
Selection.EntireColumn.Hidden = True
End Sub

Sub Populate()

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

Call Report_Format
Call Dates
Call Report_Calculations
Call Tech_Data
Call Tech_Rates
Call Tech_Names
Call Business_Names
Call Business_data
Call Title

Calculate


Call Final_Format


.ScreenUpdating = True

Range("a1").Select


End With
End Sub




:

If I understand you correctly. The macro is desinged to be run against any
sheet in the workbook. When it is runs it creates a new sheet (in the same
workbook or in a whole new workbook?) and copies some info from the original
sheet to that new sheet that you just created? There are some fairly easy
ways to do this and I am a little worried that your code is getting more
complicated that it needs to be. Post your code and lets take a look at what
you have so far.

:

The variable, Z, needs to be the name of the worksheet from which the macro
is run.

The macro runs a number of subs that first create a new sheet. Then, the
macro pulls data from the original sheet to the new sheet. Currently, I use
the actual name of the original file -- but I want the variable to be used so
that I can set it to the workbook name, no matter how the file is renamed, so
it can be called throughout the subs.

I don't know much about 'passing' variables, etc. Not sure what that entails.



:

Without being able to see your code and how you are passing and using your
variables it is difficult to respond directly to your question. In general
though this is how it works... Variables die with the procedure that creates
them. As soon as you hit end sub or end function the declared variables are
premanenetly destroyed. There are two ways around this. One is to declare the
variable not withing the preocedure but rather at the beginning of the
module. This variable lasts for the duration of the code execution. If it is
declared Private then it can be used by any procedure within the module. If
it is declared public then it can be used by an procedure anywhere.

public myGlobalVariable as string
private myModuleVariable as string

These kind of declarations sound really great but here is the downside...
They are a beast to debug. This is becuase what procedure had them last and
what is the current value. At any given time it can be very difficult to
know. Use these as sparingly as possible!

The other way to keep a varible from being destroued is to declare it
"Static". Whe this happens the variable persists even after the procedure
ends. If you call the procedure again then the variable is just how you left
it.

Chances are for what you want you will probably end up usisng a global
variable. If all you are doing is storing the name of the current worksheet
and are not changing it back and forth throughout the code executions this is
probably reasonable...

HTH


:

I currently have this line in the first of a series of subs.

Z = ActiveWorkbook.Name

This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.

However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"

Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?
Click to expand...
Click to expand...
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

Declaring a local variable once for all subs 2
Using user defined workbook name in macro worksheet functions 0
Check for unnecessary Public variable declarations and duplicate Set statements 4
Help on combining declared variable and cell formula 1
Help on combining declared variable and cell formula 1
Public Variables, Class Modules and Standard Modules 2
How To Programmatically Set A Procedure Name To A String Variable 2
Object Variable or With Block Variable Not Set 3

Top