Duplicated lines are being removed when I insert a new Row in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2439388%22%20slang%3D%22en-US%22%3EDuplicated%20lines%20are%20being%20removed%20when%20I%20insert%20a%20new%20Row%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439388%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%20new%20to%20community%20so%20go%20easy%20on%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20which%20has%20a%201st%20sheet%20that%20is%20referenced%20from%20several%20other%20sheets%20within%20the%20doc%20(Column%20A%20of%20the%20receiving%20sheets%20references%20a%20unique%20code%20from%20the%201st%20sheet%20and%20these%20run%20in%20sequence%20in%20the%20follow%20on%20sheets).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20of%20the%20follow%20on%20sheets%20need%20to%20duplicate%20a%20row%20from%20the%201st%20sheet%20and%20I%20can%20run%20some%20vba%20to%20do%20this%20no%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20want%26nbsp%3B%20to%20Insert%20a%20new%20line%20on%20Sheet%201%2C%20I%20run%20some%20vba%26nbsp%3B%20to%20insert%20the%20same%20line%20in%20the%20follow%20on%20sheets.%20However%20what%20seems%20to%20be%20happening%20is%20that%20if%20the%20line%20I%20am%20inserting%20is%20above%20the%20lines%20in%20the%20follow%20on%20sheets%20(i.e%20a%20lower%20Row%20number)%20then%20the%20subsequent%20duplicates%20that%20I%20have%20created%20in%20the%20follow%20on%20sheets%20are%20deleted%20and%20I%20don't%20want%20this%20to%20happen.%20It%20seems%20as%20though%20there%20is%20a%20refresh%20rolling%20down%20the%20sheet%20from%20the%20point%20of%20insertion%20to%20the%20bottom%20of%20the%20data%20and%20this%20is%20removing%20the%20duplicates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20one%20know%20how%20I%20can%20stop%20this%20happening%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENick%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2439388%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2442521%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicated%20lines%20are%20being%20removed%20when%20I%20insert%20a%20new%20Row%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2442521%22%20slang%3D%22en-US%22%3EHi%20Nick174%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20sounds%20like%20the%20sheet%20you%20are%20referencing%20was%20generated%20using%20power%20query%2C%20do%20you%20have%20a%20screenshot%20of%20the%20main%20sheet%3F%3CBR%20%2F%3E%3CBR%20%2F%3Echeers%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2442689%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicated%20lines%20are%20being%20removed%20when%20I%20insert%20a%20new%20Row%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2442689%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply%2C%20I'm%20afraid%20I%20can't%20show%20the%20info%20on%20the%20sheet%2C%20but%20I%20think%20it%20may%20be%20because%20I%20have%20used%20Fill%20down%20rather%20than%20Autofill%20when%20I%20insert%20the%20lines.%20I%20will%20have%26nbsp%3B%20a%20play%20with%20this%20tomorrow%20and%20will%20let%20you%20know%20if%20this%20fixes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENick%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All, new to community so go easy on me.

 

I have a spreadsheet which has a 1st sheet that is referenced from several other sheets within the doc (Column A of the receiving sheets references a unique code from the 1st sheet and these run in sequence in the follow on sheets).

 

Some of the follow on sheets need to duplicate a row from the 1st sheet and I can run some vba to do this no problem.

 

If I want  to Insert a new line on Sheet 1, I run some vba  to insert the same line in the follow on sheets. However what seems to be happening is that if the line I am inserting is above the lines in the follow on sheets (i.e a lower Row number) then the subsequent duplicates that I have created in the follow on sheets are deleted and I don't want this to happen. It seems as though there is a refresh rolling down the sheet from the point of insertion to the bottom of the data and this is removing the duplicates.

 

Any one know how I can stop this happening?

 

Many thanks in advance

 

Nick

2 Replies
Hi Nick174

It sounds like the sheet you are referencing was generated using power query, do you have a screenshot of the main sheet?

cheers

Hi @Yea_So 

 

Thanks for the reply, I'm afraid I can't show the info on the sheet, but I think it may be because I have used Fill down rather than Autofill when I insert the lines. I will have  a play with this tomorrow and will let you know if this fixes.

 

Many thanks

 

Nick