bubby68
08-08-2012, 05:45 PM
Greetings, I am looking to create a macro variable that references cell N:2 from sheet 'data update options' in my workbook. In this cell will be a directory path that has been pasted in by different users, since differentr users will sore their data in different locations..I need this macro variable to be available to all my sub routines in this module. I have many. Below is my code with the 'workbooks.open Filename= ' in bold. It has a hardcoded path now..Ideally..I would like it to be "workbooks.open Filename=mypath(the nam,e of the macro variable)
Sub RawPrevYr()
'
' RawPrevYr Macro
' Gets data from Prev_Yr.xls and store it in tab RawPrevYr.
'
Application.ScreenUpdating = False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("RawPrevYr").Select
Cells.Select
Selection.Clear
Range("A1").Select
Workbooks.Open Filename:="X:\Common\NDSS\CCDSSv2012\Bob\HYP\BC\SI_prev_yrD.xls" ' ******* Need to change to proper directory
Selection.End(xlDown).Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("graphs_directly_from_ccdss_2010.xlsm").Activate
Application.WindowState = xlMinimized
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range("B2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.NumberFormat = "#,##0"
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("prev_yr").Select
Windows("SI_prev_yrD.xls").Activate
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Close
Call GraphPrevYr
Application.ScreenUpdating = True
End Sub
Sub RawPrevYr()
'
' RawPrevYr Macro
' Gets data from Prev_Yr.xls and store it in tab RawPrevYr.
'
Application.ScreenUpdating = False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("RawPrevYr").Select
Cells.Select
Selection.Clear
Range("A1").Select
Workbooks.Open Filename:="X:\Common\NDSS\CCDSSv2012\Bob\HYP\BC\SI_prev_yrD.xls" ' ******* Need to change to proper directory
Selection.End(xlDown).Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("graphs_directly_from_ccdss_2010.xlsm").Activate
Application.WindowState = xlMinimized
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range("B2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.NumberFormat = "#,##0"
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("prev_yr").Select
Windows("SI_prev_yrD.xls").Activate
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Close
Call GraphPrevYr
Application.ScreenUpdating = True
End Sub