Excel VBA Copy / Paste macro: Ignore cells with Formulas

1
votes

I have created a tool in excel which can take two spreadsheets, and copy the content from one to another when we do an update on the sheet itself.

The tool is purely designed to be a copy / paste tool, the current code will copy and paste values from sheet to sheet.

I have to include logic into the tool to skip cells with formulas, if the tool copies and pastes the formulas which are currently in the source sheet to the target sheet, they no longer match and throw #REF errors. Any suggestions on how to put a for loop in here or something similar to allow it to check and ignore cells with formulas? I need it only to copy / paste cells with numbers or values.

Sub CopyWorkbook()
Dim wb1 As Workbook, wb2 As Workbook

wb1.Sheets("Capex").Range("H1124:AT1173").Copy
wb2.Sheets("Capex").Range("H529:AT578").PasteSpecial Paste:=xlPasteAll
wb1.Sheets("Capex").Range("H1275:AT1284").Copy
wb2.Sheets("Capex").Range("H580:AT589").PasteSpecial Paste:=xlPasteAll
3
excelvba

3 Answers

4
votes

Rather than loop cell by cell you can use SpecialCells to identify the formulae

There are two options

  1. Copy only the Constant cells to the destination
  2. Remove any Formula cells from the destination

If your formulae occur in a single contigious block then (1) works easily, else this will result in a number of areas needing to copied over. So (2) is preferable

Your first range can be covered as so.

Dim rng1 As Range
Set rng1 = Range("H1124:AT1173")
rng1.Copy [h1275]

On Error Resume Next
[h1275].Resize(rng1.Rows.Count, rng1.Columns.Count).SpecialCells(xlFormulas) = vbNullString
On Error GoTo 0
3
votes

If you really want to skip cells containing formulas, you could use this example as a start. The code assumes that only formulas start with an equals sign. Edit: expanding the example with the ranges in the question.

Sub example()
    Dim source As Range
    Dim target As Range
    Set source = ActiveSheet.Range("A1:B6")
    Set target = ActiveSheet.Range("D1:E6")
    copy_non_formulas source:=source, target:=target

    'Extended example using the ranges posted in the question
    'For the sake of formatting, I omitted the fully qualified
    'range names.
    copy_non_formulas source:=Range("H1124:AT1173"), target:=Range("H529:AT578")
    copy_non_formulas source:=Range("H1275:AT1284"), target:=Range("H580:AT589")       
End Sub


Public Sub copy_non_formulas(source As Range, target As Range)
    'Assumes that all formulas start with '=' and all non formulas do not
    Dim i As Long
    Dim j As Long
    Dim c As Range

    For i = 1 To source.Rows.Count
        For j = 1 To source.Columns.Count
            Set c = source(RowIndex:=i, ColumnIndex:=j)
            If Left(c.Formula, 1) <> "=" Then
                target(RowIndex:=i, ColumnIndex:=j).Value = c.Value
            End If
        Next j
    Next i
End Sub
0
votes

Can't you just use Paste:=xlPasteValues for all cells? That way no formulas get copied to the target sheet.