0

I am attempting to change sheet names while increasing selected dates by one (1) year.

OCT is the beginning of a new fiscal year (FY) and I'm trying to adjust accordingly. For example OCT-17, NOV-17, DEC-17, JAN-18, etc. I'm trying to change to OCT-18, NOV-18, DEC-18, JAN-19 in order to clear previous data and enter the new FY information.

Thus far, I have been able to adjust sheet names, however I am stumbling on being able to "select" the range of dates that I am attempting to adjust for the new FY. I am attempting to select the range of dates and add one (1) year to each of the dates in order to reference accurate data as the table references a pivot table as its data source. 

Dim MyDate As String
Dim Cell as Range
MyDate=Format(DateSerial(Year(Date), Month(10), 1, "yy")

If FormMonth = "OCT" then
sheet1.name = "FY" & MYDate - 3
sheet1.range("B9:M9").select
     For Each Cell in selection
          cell.value = DateAdd("yyyy", 1, CDate(cell.value))
     Next cell
End If

I have MyDate - 3 to change the sheet names as I have separate sheets that hold the previous 3 years of FY data. That successfully changes the year to the FY information I would like to present.

My script is not liking the sheet1.range("B9:M9").select.

Community
  • 1
  • 1
Derek Harden
  • 41
  • 5
  • Depending on what you're *actually* trying to so, you probably don't *need* to `Select` any cells at all. Is `Option Explicit` specified at the top of the module? What error are you getting? "my script is not liking {line of code}" doesn't say much about the problem you're having. – Mathieu Guindon Nov 16 '18 at 16:33

1 Answers1

2

You need to set sheet1 to a worksheet:

mySheetName = "FY" & MYDate - 3
Set sheet1 = Worksheets(mySheetName)

That said, you really want to avoid using Activate/Select in your code. Something like:

For Each Cell in sheet1.range("B9:M9")
    cell.value = DateAdd("yyyy", 1, CDate(cell.value))
Next cell
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
  • 1
    Thank you! It turns out I was attempting to reference the sheets' codenames, however I was referring to their index numbers. Solved, appreciate the advice. Still on the learning curve and am starting to figure some tricks out to avoid all of the activates and selections! – Derek Harden Nov 16 '18 at 18:28