Using VBA I am trying to save data within a table column into a saved column, the difficulty is the column to copy the data from changes each month as it is time related.
As an example the column titles are of a standard naming convention based on their type and the month e.g. "Cost Apr-19", "Cost May-10", "Cost Jun-19".....(other columns are similar for say Hours, but ignore that).
I have named cells which are populated by selecting the current month via a pull-down option, and then via a lookup table it sets the previous and next periods in named cells as well; these named ranges (cells) are titled "Period_Current_Cost", "Period_Last_Cost", Period_Next_Cost".
In an example, if the current month is May-19, the "Period_Current_Cost" would have a value of "Cost May-19" via the lookup table.
What I am trying to do is select the equivalent titled column in the table and copy & paste it into a different column within the table. A simple version of this, based on fixed named columns is below, but what (as a VBA novice) I am struggling with is replacing the static column name with the variable column name based on the named range (cell) "Period_Current_Cost" i.e. the code: ActiveSheet.Range("Table_EAC_LB[Total Cost (EAC)]").Select would actually result in selecting the current month "Cost May-19" via using the named variable.
Your help is really appreciated. Thanks in anticipation.
Application.ScreenUpdating = False
ActiveSheet.Range("Table_EAC_LB[Total Cost (EAC)]").Select
Selection.Copy
ActiveSheet.Range("Table_EAC_LB[Last Month Forecast Total Cost (EAC)]").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = True
As an example the column titles are of a standard naming convention based on their type and the month e.g. "Cost Apr-19", "Cost May-10", "Cost Jun-19".....(other columns are similar for say Hours, but ignore that).
I have named cells which are populated by selecting the current month via a pull-down option, and then via a lookup table it sets the previous and next periods in named cells as well; these named ranges (cells) are titled "Period_Current_Cost", "Period_Last_Cost", Period_Next_Cost".
In an example, if the current month is May-19, the "Period_Current_Cost" would have a value of "Cost May-19" via the lookup table.
What I am trying to do is select the equivalent titled column in the table and copy & paste it into a different column within the table. A simple version of this, based on fixed named columns is below, but what (as a VBA novice) I am struggling with is replacing the static column name with the variable column name based on the named range (cell) "Period_Current_Cost" i.e. the code: ActiveSheet.Range("Table_EAC_LB[Total Cost (EAC)]").Select would actually result in selecting the current month "Cost May-19" via using the named variable.
Your help is really appreciated. Thanks in anticipation.
Application.ScreenUpdating = False
ActiveSheet.Range("Table_EAC_LB[Total Cost (EAC)]").Select
Selection.Copy
ActiveSheet.Range("Table_EAC_LB[Last Month Forecast Total Cost (EAC)]").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = True
