How do I populate a list on sheet1 from data in various places on different sheets

tossit2 · Jun 7, 2016

I want data from cells under column E, in sheet 2, 3 & 4 …to automatically copy over to Column AG, sheet 1… progressively filling in the next blank cell in column AG on sheet 1…...but I only want it to copy over data from sheets 2, 3 and 4 if there is an "x" in the adjacent cell to the left of the data on sheets 2, 3 or 4.
What I am working on is a complex Task Tracker in excel. I want the various tasks throughout the book to move to a list on a "Priority Sheet" IF I place an "X" in the box directly to the left of them (in whatever sheet they are currently in).
[FONT=&quot]I am very new to all this but really trying to self-educate. I am trying really hard to ask the question correctly. Please be gentle. I will get better. Excel 2016[/FONT]

tossit2 · Jun 11, 2016

WooHoo!!! So excited!!! It seems to be working very well. Now I need to transfer everything into my real workbook and see what I can do. I am really trying to understand the code that we have in there. I understand bits and pieces of it. Is there any way you can give me a better idea of what the code is telling it to do? I have heard it referred to as Pseudo-Code but I am unsure if that is a correct term (basically just translate to English). I am not looking for a detailed blow by blow. Just an overview of it for the two pages we did. It would drastically help my learning process (and hopefully equip me better as I try and tweak my real workbook even further...so I don't have to ask so many questions...lol!) Thank you for breaking some barriers for me!!!!

tossit2 · Jun 11, 2016

Quick question: Is there anything in the code that limits it to a certain number of pages that it draws info from? I am trying to put it in my real workbook and it is telling me "subscript out of range".

L. Howard · Jun 11, 2016

I am pretty sure the number of sheets are not the problem.

Make sure the sheet named in this line of code is correct, where this sheet is the one where the tasks are written to.

If Target.Worksheet.Name = "Priorities" Then Exit Sub

Here's a run down on what the code does.

Howard

Option Explicit
Option Compare Text

When Option Explicit On or Option Explicit appears in a file, you must explicitly declare all variables by using the Dim or ReDim statements. If you try to use an undeclared variable name, an error occurs at compile time. The Option Explicit Off statement allows implicit declaration of variables.

Compare Text lets you use upper or lower case. So X and x are treated the same.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

This is a change event macro, notes changes on the worksheet (with a few exceptions) and runs the code.

Dim rngD As Range

Set rngD = Target.Parent.Range("D:D")

RngD is declared a Variable and is set to column D of the active worksheet.

If Target.Count > 1 Then Exit Sub
If Intersect(Target, rngD) Is Nothing Then Exit Sub
If Target.Worksheet.Name = "Priorities" Then Exit Sub

If more than one cell is selected then do nothing except exit sub.
If any cell out side of rngD (column D) is the Target cell then do nothing, exit sub. Only works if Target cell is in rngD (column D.)
Excludes the sheet named Priorities.

Application.ScreenUpdating = False
Application.EnableEvents = False

Prevent almost all screen flicker when the code is working, also will make the code run faster for codes that have a large amount work to do by updating the screen with the code results only after all the work is done, instead of updating screen with each iteration. Reset to TRUE at end of code.

Prevents the result of the code's work from becoming an event the code is supposed to respond to. In many cases, if not used, you will get an infinite loop where the code responds to the work it just completed because that work was an event the code is supposed to respond to, so it preforms it's task again and whoops preform it again & again & ... (Use Esc key here). Reset to TRUE at end of code.

If Target.Offset(, 1) = "" Then
Target = ""
MsgBox "No Task in " & Target.Offset(, 1).Address
Target.Select
GoTo noTsk
End If

If you enter an X in a D cell and there is nothing in the cell to the right (Target.Offset(, 1) = "" ), then the X is deleted and a message box appears informing you of the situation. The Target cell is re-selected, otherwise it would be in the next row down and the code would be ready to respond to anything you entered in that cell, inadvertently perhaps. It just brings you back to the original Target where the situation occurred. Now it's up to you to do something about it.

GoTo noTsk takes you to this line of code near the end noTsk: where Events are Enabled and Screen is Updated. Without the Enable, the code would not respond to any changes on the sheet next time one occurred.

With Target
If Target.Value = "x" Then
Sheets("Priorities").Range("AG" & Rows.Count).End(xlUp)(2) = Target.Offset(, 1)
Target = "(" & Target & ")"
End If
End With

Here is the main purpose of the code. With the Target cell you have entered an x, so the code goes to sheet Priorities to the last occupied cell of column AG, offsets down by one row and writes the value that is to the right of that x in the Target cell.

Sheets("Priorities").Range("AG" & Rows.Count).End(xlUp)(1) would overwrite the last entry in column AG with this value, Target.Offset(, 1).
.End(xlUp)(2) would enter this value, Target.Offset(, 1), in the first empty cell in column AG.
.End(xlUp)(3) would enter this value, Target.Offset(, 1), with a one row space below last entry.

Target.Offset(, 1)

Target.Offset(, 1) is the cell 1 column to the right of Target
Target.Offset(1, 1) is the cell 1 row down and 1 column right of Target
Target.Offset(-1, -1) is the cell 1 row up and 1 column to the left of target.
Target.Offset(1) is the cell 1 row down from Target

noTsk:
Application.EnableEvents = True
Application.ScreenUpdating = True

As explained above, reset all back to TRUE

End Sub

tossit2 · Jun 12, 2016

That helps this make sense to me tremendously!! Thank you for taking the time to break this down! I found the problem with my Real Workbook. It was simply user error...I had missed removing a page from the code that I had removed from the Workbook. Two questions: #1)Would it be very complicated to tell it not to put the text in column AG if it is already there? In other words if I had data typed into column AG AND I selected the same data on one of the other sheets by placing an "x" next to it, I would want it not to duplicate it on my Priorities Sheet. #2)Would it be complicated to change the destination for the data from column AG to perhaps a heading name such as "To ADD into PRIORITY SHEET" (the heading is on row 2 and not row 1...not sure if that matters). This would help as I add or delete columns in the sheet.

L. Howard · Jun 12, 2016

Question 1. Can you post a few examples of the data (tasks) that are getting transferred to AG? Need to get an idea of how to search for a duplicate data piece (task). So is a task a definitive number like 123-A-8899, or rather a narrative description of the task like "Learn Basket Weaving", or "Rob the stage coach"?

Question 2. Okay, referring to sheet "Priorities", instead of column AG the destination would always be the column with the header "To ADD into PRIORITY SHEET", and that column may be AN one time, then AF another time or AC some other time depending on what column/s you delete? If true, that is possible. But you may want to look at the sheet layout. Why do you delete columns as an ongoing normal operation of the sheet. It would seem that if you routinely delete data from the sheet, delete or clear the data, not delete the column. There might be a better way to operate the "Priorities" sheet.
If you want, you could post a link to an example "real" workbook along with why you delete columns and the general use of the sheet/s.

Howard

L. Howard · Jun 12, 2016

Try this for your question 2.

Replace current Change Event code for the one here, and there is a separate "Header_Hunter" macro just below it that demos the 'chase the header around the sheet' concept.

Grab and drag the header cell "New Tasks" and relocate it anywhere on the Priorities sheet. Then run the sub Header_Hunter(). You should get a message box telling you what column the header is now in and the cell address that the entry will be copied to, and that cell will be selected. Run a few time in a row for a test of data entry affect.

The change event macro will find the new header location also. (No message box though)

No progress on question 1, duplicate task entries, waiting for task examples.

Howard

Code:

Option Explicit
Option Compare Text

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 Dim rngD As Range
      
Dim tskCel As Range
Dim tskCol As Long

    Set rngD = Target.Parent.Range("D:D")
        
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rngD) Is Nothing Then Exit Sub
    If Target.Worksheet.Name = "Priorities" Then Exit Sub
    
 Application.ScreenUpdating = False
 Application.EnableEvents = False
  
    If Target.Offset(, 1) = "" Then
      Target = ""
      MsgBox "No Task in " & Target.Offset(, 1).Address
      Target.Select
      GoTo noTsk
    End If
    
tskCol = Sheets("Priorities").UsedRange.Find("New Tasks", LookIn:=xlValues, _
    lookat:=xlWhole).Column
Set tskCel = Sheets("Priorities").Cells(Rows.Count, tskCol).End(xlUp)(2)
 
    With Target
      If Target.Value = "x" Then
      tskCel = Target.Offset(, 1)
      End If
    End With
    
noTsk:
 Application.EnableEvents = True
 Application.ScreenUpdating = True
            
End Sub



Sub Header_Hunter()

Dim tskCel As Range
Dim tskCol As Long

 tskCol = Sheets("Priorities").UsedRange.Find("New Tasks", LookIn:=xlValues, _
    lookat:=xlWhole).Column

 Set tskCel = Cells(Rows.Count, tskCol).End(xlUp)(2)
 
 MsgBox "Column - " & tskCol & " " & "Cell - " & Cells(Rows.Count, tskCol).End(xlUp)(2).Address
  tskCel.Select
  
End Sub

L. Howard · Jun 12, 2016

I posted the wrong Header_Hunter test code, run this one a few times in succession to give you a better idea what the performance looks like.

Remember, this is just a test macro showing the concept, while the change event macro will actually do the work.

Howard

Code:

Sub Header_Hunter()

Dim tskCel As Range
Dim tskCol As Long

 tskCol = Sheets("Priorities").UsedRange.Find("New Tasks", LookIn:=xlValues, _
    lookat:=xlWhole).Column

 Set tskCel = Cells(Rows.Count, tskCol).End(xlUp)(2)
 
 MsgBox "Column - " & tskCol & " " & "Cell - " & Cells(Rows.Count, tskCol).End(xlUp)(2).Address
 tskCel = tskCel & tskCel.Offset(-1, 0) & " & " & " Here"
  tskCel.Activate
  
  
End Sub

tossit2 · Jun 13, 2016

Question 1. Can you post a few examples of the data (tasks) that are getting transferred to AG? Need to get an idea of how to search for a duplicate data piece (task). So is a task a definitive number like 123-A-8899, or rather a narrative description of the task like "Learn Basket Weaving", or "Rob the stage coach"? Answer-The tasks are narrative descriptions like "Learn Basket Weaving" or "Milk the goats".

tossit2 · Jun 13, 2016

Hello there!
This is good stuff!!
I think I get the code. However the data is still copying in column "AG" (I think due to not changing the code in the "Project Priorities" sheet itself.
This is the current code for the "Project Priorities" sheet. How do I need to alter it? I tried several different things on my own but I don't quite understand the code enough to know what I can change and what I cannot.

Option Explicit
Sub Many_T

ne_Copy()

Dim i As Long
Dim MyArr As Variant
Dim lr As Long
Dim rngE As Range, c As Range

MyArr = Array("House Projects", "Lg Outdoor Projects", "Apt Projects", "Maintenance", "Admin", "Don Proc & Fund Raising", "Connections & Events", "The Fun List", "Go & Do")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)

With Sheets(MyArr(i))
lr = .Cells(.Rows.Count, 5).End(xlUp).Row
Set rngE = .Range("E2:E" & lr)

For Each c In rngE
If c.Offset(, -1) = "x" Then
c.Copy
Sheets("Project Priorities").Range("AG" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
Else
'
End If
Next
End With
Next 'i

Application.ScreenUpdating = True
End Sub

tossit2 · Jun 13, 2016

In reference to your question about adding and deleting columns---I just wanted to be able to add and delete columns as I am tweaking the workbook. Eventually I would like to be able to have the new tasks automatically copy over (to the "Project Priorities" sheet) up under the heading that matches the name of the page name they are coming from. ie: If I type a task on the sheet "House Projects" and place an "x" next to it, I want it to automatically copy the task onto the "Project Priorities" sheet under the heading "House Projects" (and to carry over a name in column A of the same row to column A of the "Project Priorities" sheet).
I am hoping that I can learn how to do that from using the code and info you are sharing with me. I didn't think that was possible when I began this venture. I thought I would just have to transfer them to their appropriate headings manually. It is beginning to seem much more doable now! Do you think that is getting way too complicated?
I am trying to organize literally hundreds of tasks and projects for a small community organization to bring about a bit of efficiency here and to be able to use our volunteers more wisely.

How do I populate a list on sheet1 from data in various places on different sheets

tossit2

New Member

tossit2

New Member

Excel Facts

tossit2

New Member

L. Howard

Well-known Member

tossit2

New Member

L. Howard

Well-known Member

L. Howard

Well-known Member

L. Howard

Well-known Member

tossit2

New Member

tossit2

New Member

tossit2

New Member

Similar threads

Forum statistics

Share this page

How do I populate a list on sheet1 from data in various places on different sheets

New Member

New Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock