Copy from no adjacent cells to adjacent cells

Hi there,

How can I go about getting this done:

Worksheet A

Cells A1 B1 C1 D1 E1 F1
Data 1 "blank" 2 "blank" 3

Copy FORMULA TO :


WORKSHEET B[U]

CELLS F1 G1 H1
DATA =WORKSHEETA!A1 =WORKSHEETA!C1 =WORKSHEETA!E1


I tried the ordinary cut and paste from Cells F1 to G1 but instead I get the following

G1 = 0 (=worksheetA!B1) when what I wanted is to paste the formula worksheetA!C1

I am wondering if there is any way I can achieve this thru VB or some excel function.

Anyway, I tried to use VB to achieve this and here is my code :

Sub ACTest()
Dim A As String
ActiveCell.Select ' WorksheetA! CellA1
Dim B As Integer
A = ActiveCell.FormulaR1C1
Dim n As Integer



n = 1
For n = 1 To 10
B = 9 + n
ActiveCell.Select
'Range("E69").Select
'Range("E69").FormulaR1C1 = "=WorksheetB!R[25]C[" & B & "]"
ActiveCell.Formula = "=Outflow!R[25]C[" & B & "]"
ActiveCell.Offset(0, 1).Select
Next n

This works ok and enable me to paste the formulas onto worksheet b while without skipping any cells in B.
So my question is

1) How can I display the formula - ActiveCell.FormulaR1C1. (What I dis now was put A in a message box and copy it into the code. (definitely a silly way to do it)

2) After displaying it which is = WorksheerA!R[25]C[10] , how can I extract the vale of C i.e. the col number such that I can input into the formula :
B= C + n.

I really hope I am making sense. Spend many hours searching the web and trying to figure this out so any help will be really appreciated. I think this is a common problem
and there is likely to be a simple solutions.

Thank you all in advance.

Hi there,

thank you so very much for your reply. I will give it a try.

Also this is the first time I am writing to any forums so not too sure about the rules. Apologies.

Anyway yes, have to edit my post (some typo error ) and tag it. Wiil find out how. Any assistance will be well appreciated.

Cheers!