Copy non-contiguous cells and varying data types

[JimDandy]

I have a non-contiguous range of cells that contains Values, Formulas, Text or Dates. In some cases a field containing a value could instead contain a formula depending on who is filling out the form and/or what information is known. For example, a cell might contain a value, i.e. Utilities, and I might either know the total ($76,310), or I might need to perform a calculation right in the cell to obtain the total (=54236+9587+12487).

My goal is to be able to copy the information contained in this non-contiguous range to the right some 52 columns (this is a safe temporary storage location) and then be able to copy it back should I need to.

One other problem may be that the source cell might be a merged cell.

Since I cannot determine whether the information is going to be a value or a formula, and I have some text and date formats to copy as well, is there some way to copy this range and keep the data regardless of format?

My Range might look like: (F4, C5, D6:D7, D9, D13:D15, D17:D18, D22:D25, D27:D30)

F4 is a date field, C5 is a merged cell holding text, D6 is the Utilities field described above. The other fields are all formatted as dollars or percentages.

[PCI]

A small sample file with dummy will help a lot, can you prepare it?

[JimDandy]

Yes, attached is the key worksheet. The range I'm trying to capture is identified as those cells with the yellowish fill and the purple fill. I'd like to take a snapshot of those cells and copy them to an area to the right, out of sight, or if possible, hold them in memory using the "Copy Worksheet Button"

[PCI]

Is the source data always the same?
e.g. range = F4, C5, D6:D7, D9, D13:D15, D17:D18, D22:D25, D27:D30

[JimDandy]

The range remains the same, yes.

[JimDandy]

Any thoughts? I'm trying to copy the cell contents from one place to another without changing it from a formula to the results, etc. etc. I can't count on a cell to be only a value, text, a formula or what it will be. I want to copy whatever it is and keep all of the characteristics of the data itself, and not the cell format.

[PCI]

Certainly a very late answer but it could help

Option Explicit

Sub CopyData()
Dim I  As Integer
Dim WkVal, WkFor
Dim WkRange
    WkRange = Array("F4", "C5", "D6", "D7", "D9", "D13", "D14", "D15", "D17", "D18", "D22", "D23", "D24", "D25", "D27", "D28", "D29", "D30")
    For I = 0 To UBound(WkRange)
        If (Range(WkRange(I)).HasFormula) Then
            WkFor = Range(WkRange(I)).Formula
            Cells(Range(WkRange(I)).Row, 52) = Range(WkRange(I)).Formula
        Else
            WkVal = Range(WkRange(I)).Value
            Cells(Range(WkRange(I)).Row, 52) = Range(WkRange(I)).Value
        End If
    Next I
End Sub

[ThirtyTwo]

why not just create a copy of the Sheet, then you can copy stuff back from that sheet if you need to