Shifting data to columns automatically

[TC]

Dear all,
I want to shift the data in some columns to others based on the entry.
For ex:If the entry is 1 then data from Column B4:F4 should be shifted to G4:K4
If entry is 2 then data from B4:F4 should be shifted to H4:L4.Depending on the entry No the shifting column increases.
I dont know how to implement this idea and request help .
Thanks in advance .
TC

 

[DonkeyOte]

do you want to do this in VB or by formula?

by VB...

SUB COPY_DATA

99

CODEENTRY = InputBox("Please Enter the Appropriate Selection Code (1 or 2)","CODE ENTRY")

If CODEENTRY = 1 THEN

Range("B4:F4").Copy
Range("G4").PasteSpecial xlPasteValues

End If

If CODEENTRY = 2 THEN

Range("B4:F4").Copy
Range("H4").PasteSpecial xlPasteValues

End If

If CODEENTRY <> 1 AND CODEENTRY <> 2 THEN

MessageBox "You Have Failed to Enter an Applicable Code (1 or 2) - Please Re-Enter",vbokonly,"ERROR"

GoTo 99

End If

End Sub


As a formula...

If say A1 = Code (1 or 2)

in G4 = IF($A$1=1,B$4,"")

(the above formula applies to G4 ONLY)

in H4 = IF($A$1=1,C$4,B$4)

Copy this formula across to K4.

in L4 = IF($A$1=1,"",F$4)

(the above formula applies to G4 ONLY)


(without testing - I think the above should work)