vba - Copy and paste data and have it be updated automatically

Question

So I created a copy and paste function. I had help previously with an error I encountered. However, I am now wanting to make the values copy and pasted to be updated when the original date is changed. So, my original thought was to paste something like =(ws.Cells(i, j). And have a nested for loop to with the values i staying the same as below and j going in between 6 and 16. But I couldn't get that to work.

If there is a special paste function or something that I am unaware of that would be great. Is there a way to get copy and paste data but also have it still be reliant on the original (updates when the original is changed).

If there is another question with a solution to this problem then I didn't see it and I am sorry.

I have my code below. And any help would be appreciated.

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(ws.Cells(i, 6), ws.Cells(i, 16)) 'columns to be copied
  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
Worksheets("Forms").Activate
Worksheets("Forms").Cells(22, 10).Select 'going back to the Forms page

End Sub

标签： vbaexcel

Answer 1

尝试这个

Worksheets("Goals").Range("I6:I16").Copy
Worksheets("Scorecard").Paste Link:=True

如果在此过程中切换床单，我希望您不会介意..

谢谢