I have a work book with 5 identical sheets filtered to different lists

Ive linked these sheets so an edit in 1 sheets updated all the others

Its linked with the following code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
Sheets("NEXT SHEET NAME").Range(Target.Address) = Target
End Sub

NEXT SHEET NAME = name of next sheet in workbook creating a loop between them

The trouble im having is I can copy paste a single cell but if I try to copy multiple or click and drag to paste in multiple cells it does nothing. It shows hovering over paste but wont actually paste keeping the cells blank.

Anyone have any ideas?

  • Comment "On Error Resume Next" with apostrophe out and get the error, then we'll see further. – Leo Chapiro Jan 5 '17 at 9:50
  • Not sure full meaning, I can remove the on error resume next and no errors come up, I try to paste into more than one cell and it just briefly flashes up then remains blank, If I unlink the sheets it pastes fine but really I need them linked for a pretty big job soon to start. – user681413 Jan 5 '17 at 10:04
  • Just found this comment as part of a description for some VBA code "If you are not aware, there can only ever be 1 active cell in any selection and that is always the cell you Start your selection from." Would this mean the code is effectively running change on 1 cell but then as its looping round its basically deleting the cells as blank as part of the loop from matching cells in other sheets. Sure there must be a way to over come this – user681413 Jan 5 '17 at 10:39

Missing "Value", try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'On Error Resume Next
  Sheets("NEXT SHEET NAME").Range(Target.Address).Value = Target.Value
End Sub
  • 1
    ill test this now also seem to have just found a solution by changing Sheets("NEXT SHEET NAME").Range(Target.Address) = Target to Sheets("NEXT SHEET NAME").Range(Target.Address) = Target(1,1) – user681413 Jan 5 '17 at 10:53
  • 1
    Ok your method works great thank you for the help, the trouble with mine was that it copyied the 1st cell only so different data copied didn't work. Thank you again – user681413 Jan 5 '17 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.