06-03-2020 07:43 PM
06-03-2020 07:43 PM
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?
06-03-2020 08:30 PM
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.
06-03-2020 08:35 PM
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.
06-03-2020 08:51 PM
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?
06-03-2020 08:57 PM
@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.
06-03-2020 10:03 PM
@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.
06-03-2020 10:12 PM - edited 06-03-2020 10:13 PM
@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.
06-03-2020 11:04 PM
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