Preserve cell reference when copying

[flyme8]

Hi there. I've got an interesting dilemma.

I'm trying to figure out a way to preserve a cell reference between two templates that are copied and pasted.

Everytime I click a button, another two-column FORM is pasted into the MASTER SHEET. At the same time, I want to copy and paste a summary of that form into a new sheet. The summary exists as a template. I'm trying to preserve the cell references between the new FORM and the new SUMMARY sheet.

That way, I can add Form 1, Form 2, Form 3 in the MASTER SHEET, while having a corresponding summary for each Form on its own sheet.

Any thoughts?

My initial copy and paste for the FORM looks something like this.

Sub addforms()
Select Case Sheets("Attributes").Range("A1") = ""
    Case True 'paste in col A if A1 is empty
        Sheets("Defaults").Range("A1:F142").Copy Sheets("Attributes").Range("A1")
         
    Case False ' paste to next col
        Sheets("Defaults").Range("A1:F142").Copy
        Sheets("Attributes").Range("IV1").End(xlToLeft).Offset(0, 5).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

        End Select
        ActiveSheet.Paste
        
For Each cell In Range("D4:CC4")
    If cell.EntireColumn.Hidden = True Then
    Else
        If cell.Value = "calc" Then
            cell.EntireColumn.Hidden = True
    End If
    End If
    Next cell
       
    End Sub

[evertjvr]

Interesting way to put a an if statement. first time I see it (then again so is may other things here)

normally an IF look like this

if range("a1") = "" then
' some code if any for true
else
' Some code for false
end if

but back to your question now

The only cells that will be affected with a copy is formulas and the solution is not in this code (but I have been proven wrong before)
When you write the formulas and your reference to a cell or range looks like this A1 try this one $A$1 in the appropriate places. this will preserve the range references.

[flyme8]

Hi, thanks for your thoughts, though I'm not sure we're on the same page. Essentially what I'd like to do is be able to push a button that will make one copy of a summary template for columns with the heading "FORM X" and have specific values from each "FORM X" column pasted to the corresponding template. Is this as complex as it sounds?