Paste formula keeping the source formula theme

[auhom_me]

I want to input the value of B2 in cell F2,F3,F4 , value of B3 in F5,F6,F7 and so on like the followings. I tried to put the value B2 by referencing in F2,F3,F4 manually then copy all 3 cell(F2,F3,F4) and paste special (all using source theme ) immediate next 3 cell but it didn’t work rather giving the value of B5(WEDNSDAY in F5,F6,F7 instead of B3(MONDAY) value. Please help me. I am Using 2010 but I have to apply it in office 2007. Formula is preferable to vba
[TABLE="width: 410"]
<tbody>[TR]
[TD][/TD]
[TD]A1
[/TD]
[TD]B1
[/TD]
[TD][/TD]
[TD]E1
[/TD]
[TD]F1
[/TD]
[/TR]
[TR]
[TD]A1
[/TD]
[TD]DAY 1
[/TD]
[TD]DAY 2
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]SUNDAY
[/TD]
[/TR]
[TR]
[TD]A2
[/TD]
[TD]1
[/TD]
[TD]SUNDAY
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]SUNDAY
[/TD]
[/TR]
[TR]
[TD]A3
[/TD]
[TD]2
[/TD]
[TD]MONDAY
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]SUNDAY
[/TD]
[/TR]
[TR]
[TD]A4
[/TD]
[TD]3
[/TD]
[TD]TUESDAY
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]MONDAY
[/TD]
[/TR]
[TR]
[TD]A5
[/TD]
[TD]4
[/TD]
[TD="colspan: 2"]WEDNESDAY
[/TD]
[TD]2
[/TD]
[TD]MONDAY
[/TD]
[/TR]
[TR]
[TD]A6
[/TD]
[TD]8
[/TD]
[TD="colspan: 2"]THURSDAY
[/TD]
[TD]2
[/TD]
[TD]MONDAY
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]TUESDAY
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]TUESDAY
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]TUESDAY
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]WEDNESDAY
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]WEDNESDAY
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]WEDNESDAY
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

Well that was easier than I expected.

In these cells put the formula assigned:
E2 =A2, E3 =E2 and E4 =E3
Now in E5 enter this formula: =IF(MOD(E2+E3+E4,3)=0,E4+1,E4)
Copy it down as far as your need.

Now go to F2 and enter this formula:
=VLOOKUP(E2,A$2:B$6,2,FALSE)

Copy it down as far as you need.

One small warning here, I have set the range in the VLOOKUP to a maximum of 6. It won't change when you copy down. Change that 6 to maybe 8 as I assume your Column B will have Sat as the last entry.

 

[auhom_me]

Thanks. But as far as I'm concern this problem can easily be solved using Paste Special. But unfortunately I can't.

 

[BrianJN1]

Thanks for the feedback.
I note that you preferred something that was not VBA, PasteSpecial is a VBA element.

Do you want to go there?

 

[auhom_me]

Ok . Then Teach me :P

 

[BrianJN1]

Ok.
Right at the moment I do have some pressing family issues. Here is a rather simplistic code, not yet completed, but gives an idea of my direction.
I do need to condense that with other constructs within a FOR ... NEXT environment. Unfortunately I won't be able to follow this further for at least 16 hours.

In the meantime I welcome anyone else to address this further.

Sub Triples()
'
' Triples Macro
'

'
    Range("A2:B2").Select
    Selection.Copy
    Range("E2").Select
    ActiveSheet.Paste
    Range("E3").Select
    ActiveSheet.Paste
    Range("E4").Select
    ActiveSheet.Paste
    Range("A3:B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E5").Select
    ActiveSheet.Paste
    Range("E6").Select
    ActiveSheet.Paste
    Range("E7").Select
    ActiveSheet.Paste
End Sub

 

[BrianJN1]

Ok, I'm back with a simple SUB that does exactly as you require. Copy the code into the VBA editor and run it.

Sub Build()
a = 1
For b = 2 To 8
Range("A" & b & ":B" & b).Select
Selection.Copy
For c = 1 To 3
Range("E" & a).Select
ActiveCell.PasteSpecial
a = a + 1
Next
Next
End Sub

The first FOR ...NEXT loop selects the first two cells in Row2 which are then pasted into consecutive rows 3 times before moving to the next A:B selection.
The "a" keeps track of which row in column E to paste the A:B selection. Because two cells are selected it is only necessary to select one cell as the destination as the pasting automatically overflows to the adjacent cell.

Enough info?