Posts by blay

Re: VBA - Pull cell if another cell contains and create a summary

Thank you! That works. One last thing, is it possible to pull the main heading as well, so on the sample book it would be Prepare Resources, and Mobilize Resources.

Re: VBA - Pull cell if another cell contains and create a summary

Quote from skywriter;768753

It would probably help if you uploaded a sample workbook. That way we don't have to try and recreate your workbook and we have something to test the code with.

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

I have created a dummy book with the 2 sheets and macro in it with 2 Titles (bolded grey background), some sample data and check boxes setup just like my original sheet minus the 400ish lines of data and 7 Titles. I am hoping this will be ok.

Hi I saw this post, http://www.ozgrid.com/forum/showthread.php?t=190548 and I am using some of the same code however, I need a little help in customizing it. I am using the code to pull the data into a summary page and that works but I am trying to break it into titles. Thanks for your help in advance. I am still really new to vba.

I have 2 sheets within a work book. The first sheet we will call assessment. In this sheet it has multiple rows of questions (approx. 422 rows of questions). The questions are in the "C" column. On the same row in columns AD (yes) and AG (no) is where the assessor will mark the box with an X as the result. The questions are broken into 7 groups with Titles( example would be Prepare Resources and Mobilize Resources). See below. On the second sheet I call it summary. Which I want to pull the title of the section and the questions from that section in column "B" IF the cell AG (no) contains an X. I want to be able to create a summary page to hand to the manager that contains the title of the section and what questions were marked as no in that section.

[TABLE="class: cms_table_cms_table_grid, width: 500"]

Row 12

Col. C

Col. AD (Yes)

Col. AG (No)

15

Prepare Resources

16

Question asked 1

X

17

Question asked 2

X

18

Question asked 3

X

25

Mobilize Resources

26

Question asked 4

X

27

Question asked 5

X

28

Question asked 6

X

[/TABLE]

The summary page I would like to look like this with only if questions were marked with a x in the AG(no) column.

[TABLE="class: cms_table_cms_table_grid, width: 500"]

Prepare Resources

Question asked 1

Question asked 3

Mobilize Resources

Question asked 4

Question asked 6

[/TABLE]

I am getting closer. I am able to pull the data into the sheet. I am still struggling on how to separate it. I am curious can I set up multiple subs (one for prepare resources, one for mobilize)and create a range with in it to separate into different titles?

Sub PrepareResources()
 Dim WSsrc As Worksheet
 Dim WSDest As Worksheet
 Dim LRSrc As Long
 Dim LRDest As Long
 Dim AG As Long


 Set WSsrc = Worksheets("Assessment")
 Set WSDest = Worksheets("Summary")


 With WSDest
     LRDest = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
     .Range("E2:E" & LRDest).Clear
     LRDest = 2
 End With


 With WSsrc
     LRSrc = .Cells(.Rows.Count, "B").End(xlUp).Row
   
     For AG = 15 To LRSrc
         If UCase(.Range("AG" & AG)) = "X" Then
             WSDest.Range("E" & LRDest) = .Range("B" & AG)
             LRDest = LRDest + 1
         End If
     Next
 End With
 End Sub