Home

Macros- Auto Copy paste row when cell value changes

%3CLINGO-SUB%20id%3D%22lingo-sub-825194%22%20slang%3D%22en-US%22%3EMacros-%20Auto%20Copy%20paste%20row%20when%20cell%20value%20changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825194%22%20slang%3D%22en-US%22%3EI%20am%20not%20very%20experienced%20with%20excel%20so%20need%20help%20to%20do%20the%20following%3A%20I%20have%20column%20headers%20in%20columns%20A%3AG%20In%20the%20row%20below%20I%20have%20blank%20cells%20for%20data%20input.%20When%20column%20g%20has%20text%20input%2C%20I%20want%20a%20macro%20to%20automatically%20copy%20the%20row%20(sans%20data)%20and%20paste%20in%20the%20row%20below.%20For%20example%20if%20data%20is%20entered%20in%20G3%2C%20macro%20should%20copy%20row%203%2C%20insert%20and%20paste%20in%20row%204%2C%20delete%20contents%20of%20copied%20data%20in%20row%204.%20This%20sequence%20should%20repeat%20each%20time%20data%20is%20entered%20in%20any%20cell%20in%20column%20G.%20The%20very%20last%20row%20has%20formulas%20to%20calculate%20totals.%20I%20have%20the%20following%20macro%20to%20copy%2C%20insert%20row%2C%20and%20paste%3A%20Sub%20InsertRow()%20'%20'%20InsertRow%20Macro%20'%20'%20ActiveCell.EntireRow.Select%20Selection.Copy%20Selection.Insert%20Shift%3A%3DxlDown%20On%20Error%20Resume%20Next%20ActiveCell.Offset(1%2C%200).EntireRow.Cells.SpecialCells(xlCellTypeConstants).ClearContents%20On%20Error%20GoTo%200%20ActiveCell.Offset(1%2C%200).Select%20Application.CutCopyMode%20%3D%20False%20End%20Sub%20I%20have%20the%20following%20macro%20to%20run%20the%20insert%20macro%20when%20the%20value%20in%20column%20G%20changes%3A%20Private%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%20If%20Target.Address%20%3D%20%22%24G%244%22%20Then%20Application.EnableEvents%20%3D%20False%20InsertRow%20Application.EnableEvents%20%3D%20True%20End%20If%20End%20Sub%20Those%20work%20only%20when%20I%20change%20value%20in%20G4%20however%20if%20I%20try%20to%20change%20the%20macro%20to%20apply%20to%20all%20of%20column%20G%20the%20macro%20goes%20crazy.%20I%20am%20unsure%20how%20to%20modify%20macro%20to%20run%20for%20the%20entire%20column%20G.%20Thank%20you%20in%20advance%20for%20any%20suggestions%20or%20assistance!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-825194%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825915%22%20slang%3D%22en-US%22%3ERe%3A%20Macros-%20Auto%20Copy%20paste%20row%20when%20cell%20value%20changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825915%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399097%22%20target%3D%22_blank%22%3E%40Hema12%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhat%20is%20the%20big%20picture%20here%3F%20What%20are%20you%20trying%20to%20achieve%20with%20copy%20and%20pasting%20the%20rows%3F%20Do%20you%20need%20to%20duplicate%20formulas%20and%20formatting%3F%20If%20so%2C%20that%20can%20be%20done%20without%20VBA.%20Select%20the%20headers%20and%20the%20first%20row%20of%20data%20and%20use%20the%20command%20Insert%20%26gt%3B%20Table.%20With%20a%20table%2C%20ever%20row%20that%20is%20added%20below%20the%20last%20row%20will%20automatically%20have%20formulas%20and%20formatting%20applied.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Hema12
Occasional Visitor
I am not very experienced with excel so need help to do the following: I have column headers in columns A:G In the row below I have blank cells for data input. When column g has text input, I want a macro to automatically copy the row (sans data) and paste in the row below. For example if data is entered in G3, macro should copy row 3, insert and paste in row 4, delete contents of copied data in row 4. This sequence should repeat each time data is entered in any cell in column G. The very last row has formulas to calculate totals. I have the following macro to copy, insert row, and paste: Sub InsertRow() ' ' InsertRow Macro ' ' ActiveCell.EntireRow.Select Selection.Copy Selection.Insert Shift:=xlDown On Error Resume Next ActiveCell.Offset(1, 0).EntireRow.Cells.SpecialCells(xlCellTypeConstants).ClearContents On Error GoTo 0 ActiveCell.Offset(1, 0).Select Application.CutCopyMode = False End Sub I have the following macro to run the insert macro when the value in column G changes: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$G$4" Then Application.EnableEvents = False InsertRow Application.EnableEvents = True End If End Sub Those work only when I change value in G4 however if I try to change the macro to apply to all of column G the macro goes crazy. I am unsure how to modify macro to run for the entire column G. Thank you in advance for any suggestions or assistance!
1 Reply

Hello @Hema12 ,

 

what is the big picture here? What are you trying to achieve with copy and pasting the rows? Do you need to duplicate formulas and formatting? If so, that can be done without VBA. Select the headers and the first row of data and use the command Insert > Table. With a table, ever row that is added below the last row will automatically have formulas and formatting applied. 

Related Conversations
Teams no longer auto launches
trandall in Microsoft Teams on
4 Replies
What's new in Edge insider Canary Version 79.0.284
HotCakeX in Discussions on
20 Replies
Collapse and Expanding Worksheet Tabs
Nguyen Giang in Excel on
15 Replies
Copiare foglio con grafico su una nuova cartella
MCristinaMaestri in Excel on
0 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
How to copy or duplicate an existing forms quiz
Olaf Nennker in Microsoft Forms on
2 Replies