Macro for Shifting cell reference between worksheets

**clundeen** · 10-25-2011, 07:46 AM

I thought of another option that may be easier to structure - take ws ScheduleTablet and add 21 rows above my info and have those be a copy of

In other words

Rows 1-21 = Rows 22-42
run macro and
Rows 1-21 = Rows 43-63

Also
I could add columns to left of and have my info on right and the have macro delete T1:AL21

**Macdave_19** · 10-25-2011, 08:24 AM

Try This

Sub ChangeFormula()
Dim Cell As Range
Dim ShtName as String
Dim i_Len as integer

ShtName = "ScheduleTablet!B"
i_Len = Len(ShtName)


For Each Cell In Range("A3:A6")
        Cell.Formula = Left(Cell.Formula, i_Len) & Right(Cell.Formula, Len(Cell.Formula) - i_Len) + 21
Next Cell
End Sub

Let me know how it goes.

**clundeen** · 10-25-2011, 08:32 AM

Which option is this written for?

**Macdave_19** · 10-25-2011, 08:52 AM

option 1 mate

**clundeen** · 10-25-2011, 08:53 AM

Did you edit your post? I tried it on my test wb and it worked great - now your post looks different and I can't get it to work Runtime error - wierd

Also - How do I handle the multiple groups?

For Each Cell In Range("A3:A6")

For Each Cell In Range("A3:A6") ("A9:A12") ("A15:A18") or do I put them as ("A3:A6, "A9:A12, "A15:A18")?