Copy & edit formula
 

Hi Guys,
I'm battling to get a macro to perform the following:
My workbook contains 150 sheets [stock items] on each sheet variuos
information appears. I've got a summarysheet witch reflect all information
from the sheets.
It's much easier to copy the formulas from column A [which contains all 150
shhet names] on the summary sheet to column B. The sheet names remains te
same of course, but the cell adress changes. I've tried the macro recorder
without sucess.
To summirise I would like to copies the formula in column A [Sheet 1 "B2"]
to column B [Sheet 1 D2] etc.
Any help would be appreciated
Thanks
--
HJN

Copy & edit formula
 

Hi Hennie

Just a quick post, I’m not entirely sure what you are trying to do in
your post but it looks to me that the first cell in column A holds the
value in the cell B2 on Sheet 1, and from there you want to use a
modified version of the formula you used to put that information into
that cell to show the cell D2 on Sheet 1 in the first cell in column
B. In effect using the same formula but swapping out the B for a
D??? If this is the case you can use the “Replace” function to change
the column letter. The code below will do that but if this is not
what you are after please post back with some of your formulas and I
will see if I can help.

Sub ChangeCellLetter()

Dim LastRow As Integer

LastRow = [A65535].End(xlUp).Row

For i = 1 To LastRow

Cells(i, 2).Value = Replace(Cells(i, 1).Formula, "B", "D")

Next i

End Sub

Thanks

Steven