title: Using VBA code to change range in formula if it has a certain range
body: <p>I have a code that I'm working on to update a sheet that pulls information from another that has 12 columns of data based on the months of the year. The sheet that is being updated and that pulls the data from the other sheet only needs the current and previous months information. I know that this can be accomplished by finding and replacing the column letters to the current months and previous months column, but I'm trying to write VBA so that it will do it automatically upon open or by using a command button. </p>
  
  <p>I'm using the if function to check what month is the data currently showing, but the if function isn't picking it up. </p>
  
  <pre><code>Sub Button1_Click()
  '
  ' Button1_Click Macro
  '
  ' Keyboard Shortcut: Ctrl+o
  '
  
  Dim current As String
  current = Range("B8")
  
  If current = "=+'Trend Balance Sheet'!$W$9" Then
  
  Range("B:B").Value = Range("C:C").Value
  Range("F:F").Value = Range("G:G").Value
  
  Worksheets("Balance Sheet").Columns("B").Replace _
    What:="$W", Replacement:="$L", _
    SearchOrder:=xlByColumns, MatchCase:=True
  Worksheets("Balance Sheet").Columns("F").Replace _
    What:="$W", Replacement:="$L", _
    SearchOrder:=xlByColumns, MatchCase:=True
  
  Dim OriginalT As String
  Dim CorrectedT As String
  
  OriginalText = Range("B5")
  
  CorrectedT = Replace(OriginalT, "12/31/15", "01/31/16")
  
  Range("B5") = CorrectedT
  
  ElseIf current = "=+'Trend Balance Sheet'!$L$9" Then
  
  Range("B:B").Copy Range("C:C") 
  Range("F:F").Copy Range("G:G")
  
  Dim OText As String
  Dim CText As String
  OText = Range("B5")
  CText = Replace(OText, "01/31/16", "02/28/16")
  Range("B5") = CText
  
  Worksheets("Balance Sheet").Columns("B").Replace _
    What:="L", Replacement:="M", _
    SearchOrder:=xlByColumns, MatchCase:=True
  Worksheets("Balance Sheet").Columns("F").Replace _
    What:="L", Replacement:="M", _
    SearchOrder:=xlByColumns, MatchCase:=True
  
  End If
  
       Application.Goto Reference:="Button1_Click"
  
  End Sub
  </code></pre>