How to move the value/formula of one sheet to VBA?

[jbesclapez]

Hello there,

I have a kind of simple template and I would like to store it in VBA.
Usually what I do is record a macro and then copy the value in the formula bar and past it back.
That gives me the code of what is in the cell. I do this with all the active cell and job done. When I run this macro it recreates the sheet from VBA.

The sheet I have now has a lot more value and it will take me forever to do my usual method.

Do you guys have a macro that could create that?
Basically go through all active cell and copy paste the value in the formula bar and paste it back in the same cell...


Thanks for your help

 

[Tim_Excel_]

I don't follow.. Perhaps you can clarify

You want to write a VBA macro that repopulates cells with their own values?

 

[jbesclapez]

I don't follow.. Perhaps you can clarify

You want to write a VBA macro that repopulates cells with their own values?

Hi Tim,

Thanks for taking time to help me.
I will now try to clarify.
Let s say I have a value in cell A1 = 10
and a formula in cell A2= A1+10

I would like a VBA that could read the value in A1 and store it in VBA itself and the same with A2.
So the VBA will look like :

    Range("A1").FormulaR1C1 = "10"
    Range("A2").FormulaR1C1 = "=R[-1]C+1"

Then when I run this code it recreates it original sheet.

Basically you can see it has storing a template into VBA.

 

[Tim_Excel_]

I don't think this is possible, as variables are no longer populated after an "End" or "End Sub" has been passed. Perhaps someone else has more experience on this subject.

I do wonder what the use of this is, though. You can make copies of worksheets easily, not needing to completely build back up a sheet Range for Range.

Sub copysh ()

Workbooks(SOURCE).Activate
Workbooks(SOURCE).Sheets(SOURCE).Copy After:=Workbooks(TARGET).Sheets(Workbooks(TARGET).Sheets[COLOR=#303336][FONT=inherit].Co[/FONT][/COLOR][COLOR=#303336][FONT=inherit]unt[/FONT][/COLOR]) 

End Sub

I am working on a project which also uses some form of templates. I have these stored in a hidden sheet in the code source wb, which I just copy to the new wb file.

 

[jbesclapez]

I don't think this is possible, as variables are no longer populated after an "End" or "End Sub" has been passed. Perhaps someone else has more experience on this subject.

I do wonder what the use of this is, though. You can make copies of worksheets easily, not needing to completely build back up a sheet Range for Range.

Sub copysh ()

Workbooks(SOURCE).Activate
Workbooks(SOURCE).Sheets(SOURCE).Copy After:=Workbooks(TARGET).Sheets(Workbooks(TARGET).Sheets[COLOR=#303336][FONT=inherit].Co[/FONT][/COLOR][COLOR=#303336][FONT=inherit]unt[/FONT][/COLOR]) 

End Sub

I am working on a project which also uses some form of templates. I have these stored in a hidden sheet in the code source wb, which I just copy to the new wb file.

Hi Tim,

I think it is possible with a workaround.
Basically what I could imagine is that the macro reads cell A1 and then the value of A1 is in stored lets say in another place
So you end up with another sheet like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A with Cells coordinates[/TD]
[TD]Value in this cell[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]R[-1]C+1[/TD]
[/TR]
</tbody>[/TABLE]


You store those data somwhere and then you use a formula that does

="Range("&A1&").formulaR1C1="""&B1&""""
="Range("&A2&").formulaR1C1="""&B2&""""

The resulst in the excel formula will be
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Range(A1).formulaR1C1="10"[/TD]
[/TR]
[TR]
[TD]Range(A2).formulaR1C1="R[-1]C[+1"

And then you just have to recopy this in your VBA code.


It is a workaround but it does the job.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tim_Excel_]

This MDOCS article goes in depth on what you are trying to achieve. Give it a read.

 

[jbesclapez]

This MDOCS article goes in depth on what you are trying to achieve. Give it a read.

Hi Tim,

I adjusted this code to get the formula and the adress of the cell

Dim mySheet As Worksheet, myOtherSheet As Worksheet, myBook As Workbook 'Define your workbooks and worksheets as variables
Set myBook = Excel.ActiveWorkbook
Set mySheet = myBook.Sheets("codes")
Set myOtherSheet = myBook.Sheets("Sheet2")


Dim i As Integer, j As Integer 'Define a couple integer variables for counting


Dim s As String






j = 1 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28)
For i = 1 To 100 'This is the beginning the the loop which will repeat from 5 to 100 . . .
   If mySheet.Cells(i, 1).formula <> "" Then ' . . . for each digit, it will check if the cell's value is blank. If it isn't then it will . . .
      myOtherSheet.Cells(j, 2).formula = mySheet.Cells(i, 1).formula ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable.
      myOtherSheet.Cells(j, 1).formula = mySheet.Cells(i, 1).Address ' This will copy the adress of the cell where the formula is
      j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2.
   End If
Next i 'This triggers the end of the loop and moves on to the next value of "i".

I end up with 2 columns in sheet2 and in the third column I add this formula that then will be used to generate my code

="Range("&A1&").formulaR1C1="""&B1&""""

There is still work to do on that....