0

So I am just trying to create a copy and paste function. I want it to be a bit more dynamic than some of the similar problems I have seen on here. When I get to the line (code below) "set rng = ws.Range(Cells(i, 7), Cells(i, 12))" I get the error 1004 Application-defined or object defined error. Also the button is in the sheet named "Forms".

I originally didn't have " set rng = ws." before Range(Cells(i, 7), Cells(i, 12)) and I didn't get an error but It would copy data from "forms" rather than from "Goals". Because of how the excel spreadsheet is formatted the numbers in the function are correct, I believe (not sure if that is relevant).

Ultimately, I am trying to get the error to go away, and to get it to work properly. Any help or suggestions welcome. 

Private Sub CommandButton1_Click()

Dim rng As Range
Dim ws As Worksheet
Set ws = Worksheets("Goals")
a = Worksheets("Goals").Cells(Rows.Count, 7).End(xlUp).Row

  For i = 2 To a

  If Worksheets("Goals").Cells(i, 20).Value = "Red" Then

      ws.Activate
      Set rng = ws.Range(Cells(i, 7), Cells(i, 12))
      rng.Copy 
      Worksheets("Scorecard").Activate
      b = Worksheets("Scorecard").Cells(Rows.Count, 1).End(xlUp).Row
      Worksheets("Scorecard").Cells(b + 1, 2).Select
      ActiveSheet.Paste
      Worksheets("Goals").Activate

  End If

Next


Application.CutCopyMode = False

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
FlexOff
  • 5
  • 2

2 Answers2

2

Try,

Set rng = ws.Range(ws.Cells(i, 7), ws.Cells(i, 12))

The cells that define range need to have defined parent worksheets that match range,

See Is the . in .Range necessary when defined by .Cells? for more information.

2

I think you need to include the ws. in front of both Cells references too, otherwise they may be pulling from a different sheet to the ws.Range call:

Set rng = ws.Range(ws.Cells(i, 7), ws.Cells(i, 12))
Daniel Bailey
  • 159
  • 2
  • 7