0

What I'm trying to do is copy an entire sheet into VBA as values. Instead of a copy/paste function, what I want to do is have the code write each cell value so that the original page copied from is not required to be accessible.

So instead of something like:

Range("D9").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

It would look like this:

Range("D9").Select
    ActiveCell.FormulaR1C1 = "What ever cell value"

I would like to copy an entire sheet and save it in VBA as formulas for each cell. As text is fine, no actual formulas will be present in the data I need to copy, if that makes it easier.

Thanks so much.

ANSWER:

In the comments are better solutions than the one I was trying to find. However I have reasons for trying to figure out this code. I'm creating complex macros for excel noobs. I don't want to have to trust them to create the data sheets, or even copy them from other workbooks, that are needed for the macros I will give them.

The code I wrote won't work in many cases, such as if cells contain quotation marks or formulas, but it will work for what I'm going to use it for. A few lines of code will be added before I use it to format the entire sheet as text and trim each cell ahead of time, but here's the basic code:

Sub VBA_Formulas()

Dim r As Range
Set r = Selection

For Each r In Intersect(Selection, ActiveSheet.UsedRange)
    r.Value = "Range(" & Chr(34) & r.Address & Chr(34) & ").Value = " & Chr(34) & r.Value & Chr(34)
    Next r

End Sub

I will then copy all the cells the code ran on into VBA to create the sheets needed to run the macros I will be passing out. There's more code involved but I'm sharing the code I figured out as a solution to the specific question I asked.

Thank you all for your input, I will probably use some of the array techniques in other codes I write at some point.

Sabatino Ognibene
  • 142
  • 13
  • 1
    Take a look at [Arrays and Ranges](http://www.cpearson.com/excel/ArraysAndRanges.aspx). – BigBen Nov 11 '19 at 18:49
  • 1
    You will also want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Nov 11 '19 at 18:53
  • Are you saying that you would want to highlight a set of cells and run a macro that turns that set into vba statements like `Range("A1").Value = "a one" : Range("B1").Value = "b one"` etc? – Marcucciboy2 Nov 11 '19 at 19:04
  • If that's the case, this might be a bit of an x/y problem wherein you're asking a for a method to solve a problem which has a different, better solution – Marcucciboy2 Nov 11 '19 at 19:04
  • @Marcucciboy2 - Yes, you are understanding what I want to do; I want to highlight a set of cells and run a macro that turns that set into VBA statements like Range("A1").value = "a one" etc. If there's a different, better better solution than the way I'm trying to do it, please enlighten me. – Sabatino Ognibene Nov 11 '19 at 20:24
  • Assuming `myArray` is declared as type Variant. `myArray` = `myRange` will populate `myArray` with the values in `myRange` as a 2D array, where the first dimension equates to the rows, and the 2nd to the columns. For example, if `myRange = B2:D10`, `myArray(2,3)` would contain the value from `D3` – Ron Rosenfeld Nov 11 '19 at 20:56
  • I appreciate all the input about arrays and I read the entire article linked in the first comment. However, I really would like to know if there's a way to export a range into individual cell formulas which VBA can use without pulling data from the array in the future. Formulas such as: Range("A1").value = "a one" – Sabatino Ognibene Nov 12 '19 at 03:35
  • I think that the most common solution is to save a hidden copy of the sheet that you want to clone later and then you can easily just paste from that hidden sheet whenever you need that data again. Since VBA is doing all the copying and pasting, you only need to be bothered with knowing how to execute it – Marcucciboy2 Nov 12 '19 at 18:39
  • All your answers are better solutions However I have reasons for trying to figure out this code. I'm creating codes for complete excel noobs whom I don't even want to teach how to hide sheets. The code I wrote won't work in many cases, such as if cells contain quotation marks or formulas, but it will work for what I'm going to use it for. I wrote this: Sub VBA_Formulas() Dim r As Range Set r = Selection For Each r In Intersect(Selection, ActiveSheet.UsedRange) r.Value = "Range(" & Chr(34) & r.Address & Chr(34) & ").Value = " & Chr(34) & r.Value & Chr(34) Next r End Sub – Sabatino Ognibene Nov 13 '19 at 02:59

0 Answers0