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]

L. Howard · Jun 7, 2016

Hi tossit2, welcome to the board.

How about start with this, in a standard module. Make sure the sheet names in the code match your sheet names, as here.

MyArr = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5"), if different names, then change here, as well as the number of sheets.

Howard

Code:

Option Explicit

Sub Many_To_One_Copy()

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

MyArr = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
               
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("Sheet1").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 8, 2016

Thanks so much Howard for taking the time to help me. I am not sure what I am doing wrong. I created a dummy workbook and put the module in that you gave me but it doesn't seem to be doing anything. Is there a way I can send you a copy of the dummy workbook with the code in it?

L. Howard · Jun 8, 2016

tossit2 said:
Thanks so much Howard for taking the time to help me. I am not sure what I am doing wrong. I created a dummy workbook and put the module in that you gave me but it doesn't seem to be doing anything. Is there a way I can send you a copy of the dummy workbook with the code in it?

First make sure the dummy workbook has five worksheets, named Sheet1 to Sheet5

Then right click the Sheet1 tab > View Code > this is the sheet vb editor.

Look up to the top left in the menu bar and find Insert, click Insert and in the drop down select Module.

This will insert a standard module and the large white space is where you will copy the code into.

Then back to the sheet1.

With sheets 1 to 5, values of text or numbers or formula results in columns E, either an upper or lower case X x in column D, those values should be listed in column AG on sheet 1.

If still having problems then post a link to your dummy workbook and I will take a look at it. You cannot attach workbooks here but you can use one of the link utilities for posting a link, I use Drop Box but there are others.

Howard

tossit2 · Jun 8, 2016

I have done all those things Howard and still cannot get it to do anything. link to dummy workbook as requested: https://ln.sync.com/dl/59792df90#v2uawrif-ymnkbxap-tegankak-tqn9tire

tossit2 · Jun 9, 2016

I found the problem in the code that was keeping it from doing anything! I changed the capital X to a small x (which is what I used in the workbook) so the code seems to be working. Two Problems: 1)It only works when I go into the VBA and tell it to run. 2)Each time I run the code it copies the same info over again, thereby duplicating the tasks on the Priority Sheet (sheet 1).

L. Howard · Jun 9, 2016

tossit2 said:
I found the problem in the code that was keeping it from doing anything! I changed the capital X to a small x (which is what I used in the workbook) so the code seems to be working. Two Problems: 1)It only works when I go into the VBA and tell it to run. 2)Each time I run the code it copies the same info over again, thereby duplicating the tasks on the Priority Sheet (sheet 1).

For the X or x, we can make the code non-case sensitive.

For 1) you can assign the code to a button or shape on the sheet where you would just click on that button/shape and run the code. Or you can do a keystroke Alt + f8 and select the macro in the pop up box then click RUN.

For 2) What are you expecting the code to do with the info on the sheets other than copy any task with an x in the adjacent cell to the Priorities sheet, column AG? If the data remains the same on the sheets and you run the macro, it will find the x's (new or old) and copy the task to Priorities sheet, column AG below the last entries.

I notice the word automatically in the instructions in cell AG2. Are you wanting to populate Priorities sheet with the task from any sheet at the moment you enter an X next to a task on one of the sheets? Where you may select a sheet and add one or two x's to a task/s on that sheet, expecting those tasks to now be listed on Priorities sheet?

Also, can we expect a sheet to have no tasks and therefore no x's? A sheet to have blank rows in column E? A sheet where the list of E column tasks start a few rows down from the Header, i.e. E6:E18?

Howard

tossit2 · Jun 9, 2016

L. Howard said:
For the X or x, we can make the code non-case sensitive.

For 1) you can assign the code to a button or shape on the sheet where you would just click on that button/shape and run the code. Or you can do a keystroke Alt + f8 and select the macro in the pop up box then click RUN.
That would be fine to assign a button to click on to run the code.

For 2) What are you expecting the code to do with the info on the sheets other than copy any task with an x in the adjacent cell to the Priorities sheet, column AG?

If the data remains the same on the sheets and you run the macro, it will find the x's (new or old) and copy the task to Priorities sheet, column AG below the last entries. That is primarily what I need it to do. However, I want to prevent it from duplicating the same data every time the code is run. If the data is already on the Priorities Sheet in column AG (Sheet 1) I don’t want it to copy it over again. I only want it to copy over any data with an “x” beside it that is not already on the Priority Sheet in column AG (Sheet 1). Is this possible?

I notice the word automatically in the instructions in cell AG2. Are you wanting to populate Priorities sheet with the task from any sheet at the moment you enter an X next to a task on one of the sheets? Where you may select a sheet and add one or two x's to a task/s on that sheet, expecting those tasks to now be listed on Priorities sheet?

That was my plan. But I can certainly work with pushing a button on the Priorities Sheet (Sheet 1) to update it each time (if I can get it to quit duplicating).

Also, can we expect a sheet to have no tasks and therefore no x's? Yes that is a possibility.
A sheet to have blank rows in column E? Yes there will definitely be blank rows between many of the tasks.
A sheet where the list of E column tasks start a few rows down from the Header, i.e. E6:E18? Yes this may often be the case.

I have learned soooo much just in working with the code you sent me to begin with. Thank you Howard for sticking with me. I love your specific questions as well as your explanations!

Howard

L. Howard · Jun 9, 2016

Okay, with your answers, that pretty much takes us on a slightly different track.

It looks like a Change_Event code will do the trick.

I'll work one up for you to try.

Howard

L. Howard · Jun 9, 2016

Give this a try.

This goes in the ThisWorkbook module. Find it just below the sheet names and above Modules (if any inserted) in the VB editor. Double click ThisWorkbook and copy/paste in there.

Back to the sheets, where you will enter either X or x in column D and the task is listed on sheet Priorities, column AG.

The X or x you entered will revert to x * for the notation that task has been transferred to Priorities sheet. I'm not sure if this is necessary, but I envisioned a large list of X's and empty cells in D column, where you may find yourself in a cell with an X and not be sure if the code had transferred the Task. If the * is burdensome to you, then it is easily removed in the code. May not be needed at all.

Howard

Code:

Option Explicit
Option Compare Text

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

 Dim rngD As Range
       
    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
 
    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
    
noTsk:
 Application.EnableEvents = True
 Application.ScreenUpdating = True
            
End Sub

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

tossit2

New Member

Excel Facts

L. Howard

Well-known Member

tossit2

New Member

L. Howard

Well-known Member

tossit2

New Member

tossit2

New Member

L. Howard

Well-known Member

tossit2

New Member

L. Howard

Well-known Member

L. Howard

Well-known 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

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Well-known 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