Macro for Shifting cell reference between worksheets

[clundeen]

I made a quick exanple to demonstrate what I need

wk1 has a group of cell filled in red and named "Shiftschedule"

The defined cells in wk1 look at ws "ScheduleTablet" for information. I am looking for a macro to take the reference cells and shift down 21 rows each - I am open to options - but this one is beyond my experience - THANKS!!!

in otherwords wk1

Before Macro
A3=ScheduleTablet!B3
A4=ScheduleTablet!B4
A5=ScheduleTablet!B5
A6=ScheduleTablet!B6


After Macro
A3=ScheduleTablet!B24
A4=ScheduleTablet!B25
A5=ScheduleTablet!B26
A6=ScheduleTablet!B27


A3=ScheduleTablet!B45......

[clundeen]

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]

Try This

Please Login or Register  to view this content.

Let me know how it goes.

[clundeen]

Which option is this written for?

[Macdave_19]

option 1 mate

[clundeen]

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")?

[clundeen]

Here is what I have - getting mismatch error

highlighting - Cell.Formula = Left(Cell.Formula, i_Len) & Right(Cell.Formula, Len(Cell.Formula) - i_Len) + 21

[Macdave_19]

just replace i_len with 17 buddy should be fine

[Macdave_19]

As for the multiple ranges yes you're almost there:

Please Login or Register  to view this content.

Hope that helps and please add to my rep

Ta

[clundeen]

GREAT!!!!

Last bit of clean up - I have 3 separate ws that are all linked to same master ScheduleTablet

wk1, wk2, wk3 (duplicated form so they has same set of cells) just different rows they refer to

wk1 A3:A6 = ScheduleTablet!B3:B6
wk2 A3:A6 = ScheduleTablet!B10:B13
wk3 A3:A6 = ScheduleTablet!B17:B20

Do I need to add anything to cover these added ws?

I will comment on you merit for sure!!!!!

[clundeen]

What does the 17 mean? Sorry for being a pain - I am trying to learn so I won't need as much help

[Macdave_19]

Hi clunden its the length in characters of the sheetname and the "!B" bit together