CODEWITHSUNDEEP
vbaexcel
Will
Will

Reputation: 21

VBA Excel copying range of formulas from one worksheet to another

I am trying to copy a couple of ranges with formulas from one worksheet to another. As the contents of the "Schedule" ranges change, they are copied to different ranges on the "Data_Design" worksheet. The code that I have works intermittently but I cannot determine why it is not stable and repeatable. I have tried a couple of different variations of code but to no avail. When I get the runtime error (1004) it is at the first line of code equating the ranges. Any idea why the 1004?

Sub Design_Save()
Dim A, i, D, Ans As Integer

Redo:
D = Application.InputBox("Enter Design Number" & vbNewLine & vbNewLine & "Note: Default value = current stage number", "Design Number Assignment", Worksheets("Job").Cells(10, 3).Value, Type:=1)

    For i = 2 To 101    ' Revise the 500 to include all of your values
       A = Worksheets("Data_Design").Cells(i, 1).Value
       If A = D Then
            Ans = MsgBox("Design Number " & D & " already exists." & vbNewLine & vbNewLine & "Overwrite exiting design?", vbYesNo + vbQuestion, "Overwrite Design")
            If Ans = vbYes Then
                Save_Design (D)
            Else
                GoTo Redo
            End If
       Else
            Exit Sub
       End If
    Next i
End Sub

Sub Save_Design(A As Integer)
Dim r1, r2 As Integer
Dim dd1, dd2, dd3, dd4, dd101, dd102, dd103, dd104 As Range

If A = 0 Then
  r1 = 1
  Else
    r1 = A * 100
End If
r2 = r1 + 49

Worksheets("Data_Design").Range(Cells(r1, 2), Cells(r2, 6)).Formula = Worksheets("Schedule").Range("C5:G54").Formula
Worksheets("Data_Design").Range(Cells(r1, 7), Cells(r2, 8)).Formula = Worksheets("Schedule").Range("I5:J54").Formula
Worksheets("Data_Design").Range(Cells(r1, 9), Cells(r2, 23)).Formula = Worksheets("Schedule").Range("L5:Z54").Formula
Worksheets("Data_Design").Range(Cells(r1, 24), Cells(r1, 38)).Formula = Worksheets("Schedule").Range("L3:Z3").Formula

End Sub

Upvotes: 1

Views: 1844

Answers (1)

Scott Craner
Scott Craner

Reputation: 152475

You need to assign proper parentage to the Cells() range objects. Try this

With Worksheets("Data_Design")
    .Range(.Cells(r1, 2), .Cells(r2, 6)).Formula = Worksheets("Schedule").Range("C5:G54").Formula
    .Range(.Cells(r1, 7), .Cells(r2, 8)).Formula = Worksheets("Schedule").Range("I5:J54").Formula
    .Range(.Cells(r1, 9), .Cells(r2, 23)).Formula = Worksheets("Schedule").Range("L5:Z54").Formula
    .Range(.Cells(r1, 24), .Cells(r1, 38)).Formula = Worksheets("Schedule").Range("L3:Z3").Formula
End With

Using the with block makes it so anything that starts with a . is assigned to the with block parentage.

Upvotes: 2

Related Questions