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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies