CODEWITHSUNDEEP
excelvba
Derek Harden
Derek Harden

Reputation: 41

Selecting a range of cells

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.

Upvotes: 0

Views: 94

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

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

Upvotes: 2

Related Questions