| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable holding across multiple subs?
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
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" wrote: 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
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 In article , says... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
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" wrote: 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 In article , says... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
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" wrote: 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" wrote: 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
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" wrote: 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" wrote: 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" wrote: 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
That did it -- it only had problems with a few un-dim'd variables. Slow as
anything, but it created the report. "Jim Thomlinson" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable holding across multiple subs?
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" wrote: That did it -- it only had problems with a few un-dim'd variables. Slow as anything, but it created the report. "Jim Thomlinson" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| holding the result constant | Excel Worksheet Functions | |||
| holding a value after first calcuation | Excel Worksheet Functions | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
| Cells not holding format | Excel Discussion (Misc queries) | |||
| Copy into Row holding Button | Excel Programming | |||
Linear Mode
