Automatically import data from one sheet into another

%3CLINGO-SUB%20id%3D%22lingo-sub-1439085%22%20slang%3D%22en-US%22%3EAutomatically%20import%20data%20from%20one%20sheet%20into%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439085%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%202%20sheets%20within%20the%20same%20workbook.%20Sheet1%20contains%20a%20list%20of%20%22General%20Items%22.%20Sheet2%20contains%20a%20list%20of%20%22All%20Items%22%20which%20includes%20the%20general%20items%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20wanting%20Sheet2%20to%20grab%20all%20the%20items%20listed%20under%20%22General%20Items%22%20in%20Sheet1%20and%20automatically%20add%20them%20to%20Sheet2%20under%20a%20heading.%20Further%20to%20this%2C%20when%20a%20new%20row%20is%20added%20to%20%22General%20Items%22%20in%20Sheet1%2C%20I%20need%20this%20to%20automatically%20update%20Sheet2%20to%20include%20that%20new%20row%20and%20data%20pushing%20all%20data%20below%20down%20as%20it%20does%20so.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20anything%20added%20to%20the%20list%20in%20Sheet1%20is%20automatically%20sent%20to%20a%20specific%20spot%20in%20Sheet2%20(ideally%20copying%20formatting%20also).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20this%20be%20done%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1439085%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439135%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20import%20data%20from%20one%20sheet%20into%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439135%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%2F688508%22%20target%3D%22_blank%22%3E%40tomKG%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eduplicating%20data%20is%20seldom%20a%20good%20idea%20and%20often%20not%20necessary.%20Why%20do%20you%20need%20the%20data%20in%20one%20sheet%3F%20And%20if%20you%20need%20it%20in%20one%20sheet%2C%20why%20have%20a%20separate%20sheet%20for%20data%20entry%3F%20Can't%20the%20General%20items%20be%20maintained%20in%20the%20sheet%20with%20the%20Specific%20items%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20should%20happen%20in%20your%20two%20sheet%20scenario%20when%20someone%20edits%20a%20General%20Item%20after%20it%20was%20already%20copied%20to%20the%20other%20sheet%3F%20It%20will%20be%20a%20major%20effort%20to%20keep%20the%20items%20in%20sync%20on%20both%20sheets%2C%20so%20looking%20into%20the%20bigger%20picture%20of%20what%20you%20would%20like%20to%20achieve%20may%20help%20find%20a%20better%20approach%20that%20may%20be%20different%20from%20what%20you%20are%20asking%20for%20in%20your%20question.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439149%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20import%20data%20from%20one%20sheet%20into%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet2%20is%20intended%20to%20be%20a%20template%20which%20will%20dupilcated%2030%2B%20times.%20Each%20new%20sheet%20made%20from%20Sheet2%20will%20have%20completely%20different%20data%20listed%20under%20%22Specific%20Items%22.%20The%20%22General%20items%22%20list%20is%20required%20to%20be%20uniform%20across%20all%20those%20new%20sheets.%20As%20such%2C%20I%20want%20the%20General%20items%20to%20be%20on%20one%20sheet%20which%2C%20when%20updated%2C%20is%20transferred%20to%20all%20the%2030%2B%20sheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439166%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20import%20data%20from%20one%20sheet%20into%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F688508%22%20target%3D%22_blank%22%3E%40tomKG%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20few%20more%20questions%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20the%20special%20items%20sheets%20get%20created%2C%20are%20there%20any%20special%20items%20on%20there%2C%20or%20is%20it%20just%20the%20heading%20for%20special%20items%20under%20the%20list%20of%20general%20items%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20the%2030%20sheets%20have%20been%20created%2C%20do%20you%20need%20them%20updated%20from%20the%20General%20items%20sheet%20when%20a%20general%20item%20is%20added%20or%20changed%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439177%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20import%20data%20from%20one%20sheet%20into%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BWhen%20the%20new%20sheets%20are%20created%20from%20the%20template%2C%20the%20%22Specific%20Items%22%20list%20will%20be%20empty%20initially%2C%20so%20it%20will%20just%20be%20a%20heading%20with%20no%20content%20underneath.%20They%20will%20then%20be%20populated%20later.%20So%20each%20sheet%20will%20have%20completely%20different%20data%20under%20%22Specific%20Items%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22General%20Items%22%20will%20remain%20the%20only%20uniform%20data%20set%20across%20all%20the%20sheets%20created%20from%20the%20template%20(Sheet2)%20and%20must%20remain%20uniform%20as%20it%20is%20updated%20over%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439243%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20import%20data%20from%20one%20sheet%20into%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439243%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F688508%22%20target%3D%22_blank%22%3E%40tomKG%3C%2FA%3E%26nbsp%3BSo%2C%20when%20the%20new%20sheets%20are%20created%2C%20wouldn't%20it%20make%20sense%20to%20use%20the%20General%20Items%20sheet%20as%20the%20template%20and%20just%20add%20a%20heading%20for%20Specific%20items%20below%20that%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20the%20sheets%20have%20been%20created%2C%20updating%20with%20new%20values%2C%20making%20changes%20to%20existing%20values%20and%20inserting%20rows%20above%20the%20Specific%20items%20will%20be%20really%2C%20really%20difficult.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439256%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20import%20data%20from%20one%20sheet%20into%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%20No%20because%20the%20General%20items%20will%20be%20slowly%20changing%20over%20time%20and%20the%20templates%20will%20be%20created%20over%20the%20course%20of%20many%20months.%20The%2030th%20sheet%20may%20be%20made%206%20months%20after%20the%201st%20sheet%2C%20but%20they%20will%20need%20to%20have%20uniform%20General%20items%20If%20I%20use%20the%20general%20items%20sheet%20as%20a%20template%2C%20the%20General%20items%20list%20will%20vary%20from%20sheet%20to%20sheet%20based%20on%20when%20it%20was%20duplicated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439316%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20import%20data%20from%20one%20sheet%20into%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F688508%22%20target%3D%22_blank%22%3E%40tomKG%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlace%20the%20following%20code%20for%20%3CSTRONG%3ESheet%20Change%20Event%3C%2FSTRONG%3E%20on%20%3CSTRONG%3ESheet1%20Module%3C%2FSTRONG%3E%20and%20see%20if%20this%20works%20as%20desired.%3C%2FP%3E%3CP%3ETo%20place%20the%20code%20on%20Sheet1%20Module%2C%20%3CSTRONG%3Eright%20click%20on%20Sheet1%20Tab%3C%2FSTRONG%3E%20and%20choose%20%3CSTRONG%3EView%20Code%3C%2FSTRONG%3E%20and%20then%20paste%20the%20code%20given%20below%20into%20the%20opened%20code%20window%20and%20save%20your%20file%20as%20Macro-Enabled%20Workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3ECode%20on%20Sheet1%20Module%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0AIf%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%0A%0ADim%20rng%20As%20Range%0ADim%20r%20%20%20As%20Long%0ADim%20dlr%20As%20Long%0A%0AIf%20Not%20Intersect(Target%2C%20Range(%22B%3AB%22))%20Is%20Nothing%20Then%0A%20%20%20%20If%20Target.Row%20%26gt%3B%202%20Then%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20If%20Target%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%20Target.Row%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20r%20%26lt%3B%26gt%3B%203%20And%20Target.Offset(-1%2C%200)%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MsgBox%20%22Please%20fill%20the%20previous%20row%20first%20and%20then%20try%20again...%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Application.Undo%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20GoTo%20Skip%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Sheet2.Range(%22A%3AA%22).Find(what%3A%3D%22General%20Items%22%2C%20lookat%3A%3DxlWhole%2C%20MatchCase%3A%3DFalse)%0A%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20rng.Offset(1%2C%200)%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dlr%20%3D%20rng.Offset(1%2C%200).Row%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dlr%20%3D%20rng.End(xlDown).Row%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20r%20%26amp%3B%20%22%3AB%22%20%26amp%3B%20r).Copy%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Sheet2.Range(%22A%22%20%26amp%3B%20dlr).Insert%20shift%3A%3DxlDown%0A%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MsgBox%20%22The%20header%20'General%20Items'%20was%20not%20found%20in%20column%20A%20on%20Sheet2.%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20GoTo%20Skip%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20If%0ASkip%3A%0AApplication.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have 2 sheets within the same workbook. Sheet1 contains a list of "General Items". Sheet2 contains a list of "All Items" which includes the general items list.

 

I am wanting Sheet2 to grab all the items listed under "General Items" in Sheet1 and automatically add them to Sheet2 under a heading. Further to this, when a new row is added to "General Items" in Sheet1, I need this to automatically update Sheet2 to include that new row and data pushing all data below down as it does so.

 

Basically, anything added to the list in Sheet1 is automatically sent to a specific spot in Sheet2 (ideally copying formatting also).

 

How can this be done?

7 Replies
Highlighted

Hi @tomKG ,

 

duplicating data is seldom a good idea and often not necessary. Why do you need the data in one sheet? And if you need it in one sheet, why have a separate sheet for data entry? Can't the General items be maintained in the sheet with the Specific items?

 

What should happen in your two sheet scenario when someone edits a General Item after it was already copied to the other sheet? It will be a major effort to keep the items in sync on both sheets, so looking into the bigger picture of what you would like to achieve may help find a better approach that may be different from what you are asking for in your question.

 

Highlighted

@Ingeborg Hawighorst 

 

Sheet2 is intended to be a template which will dupilcated 30+ times. Each new sheet made from Sheet2 will have completely different data listed under "Specific Items". The "General items" list is required to be uniform across all those new sheets. As such, I want the General items to be on one sheet which, when updated, is transferred to all the 30+ sheets.

Highlighted

@tomKG 

A few more questions:

 

When the special items sheets get created, are there any special items on there, or is it just the heading for special items under the list of general items?

 

After the 30 sheets have been created, do you need them updated from the General items sheet when a general item is added or changed?

Highlighted

@Ingeborg Hawighorst When the new sheets are created from the template, the "Specific Items" list will be empty initially, so it will just be a heading with no content underneath. They will then be populated later. So each sheet will have completely different data under "Specific Items". 

 

"General Items" will remain the only uniform data set across all the sheets created from the template (Sheet2) and must remain uniform as it is updated over time.

 

 

 

 

Highlighted

@tomKG So, when the new sheets are created, wouldn't it make sense to use the General Items sheet as the template and just add a heading for Specific items below that? 

 

Once the sheets have been created, updating with new values, making changes to existing values and inserting rows above the Specific items will be really, really difficult.

Highlighted

@Ingeborg Hawighorst  No because the General items will be slowly changing over time and the templates will be created over the course of many months. The 30th sheet may be made 6 months after the 1st sheet, but they will need to have uniform General items If I use the general items sheet as a template, the General items list will vary from sheet to sheet based on when it was duplicated. 

Highlighted

@tomKG 

 

Place the following code for Sheet Change Event on Sheet1 Module and see if this works as desired.

To place the code on Sheet1 Module, right click on Sheet1 Tab and choose View Code and then paste the code given below into the opened code window and save your file as Macro-Enabled Workbook.

 

Code on Sheet1 Module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

Dim rng As Range
Dim r   As Long
Dim dlr As Long

If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.Row > 2 Then
        Application.EnableEvents = False
        If Target <> "" Then
            r = Target.Row
            If r <> 3 And Target.Offset(-1, 0) = "" Then
                MsgBox "Please fill the previous row first and then try again...", vbExclamation
                Application.Undo
                GoTo Skip
            End If
            
            Set rng = Sheet2.Range("A:A").Find(what:="General Items", lookat:=xlWhole, MatchCase:=False)
            
            If Not rng Is Nothing Then
                If rng.Offset(1, 0) = "" Then
                    dlr = rng.Offset(1, 0).Row
                Else
                    dlr = rng.End(xlDown).Row + 1
                End If
                
                Range("A" & r & ":B" & r).Copy
                Sheet2.Range("A" & dlr).Insert shift:=xlDown
            Else
                MsgBox "The header 'General Items' was not found in column A on Sheet2.", vbExclamation
                GoTo Skip
                Exit Sub
            End If
            
        End If
    End If
End If
Skip:
Application.EnableEvents = True
End Sub