Help in creating a function or macro to run on data

[Sawright]

I need help badly

I'm trying to create a checklist that is specific and develops the populates the checklist based on decisions the user chooses on the first sheet "Choose" For simplicity lets say on sheet label "choose" I have in column 'A' the following: Apple, Pear, Watermelon and next to each of them in column 'B' I have a box for them to place an "X" There is a separate sheet labeled the same for each "Apple, Pear, Watermelon" Each of those sheets contain data in cells from A1:D20

So a user goes to sheet "Choose" and places an X in box next to Apple and Watermelon Then hits control 'R' Or whatever function:

I then need it to go to the "Apple" sheet and copy A1:D20 data and paste it on a new sheet labeled "Checklist" Then paste the same for "Watermelon" on the same sheet "Checklist" under the Apple information without any spaces or rows between them.

Is this possible?


Thanks so very much

 

[skorpionkz]

I will check it out when I'll back from work.

 

[skorpionkz]

Ok fixed

there was 2 problems

First: You added macro to incorrect place you need to add it under the module not to the worksheet - fixed see attachment. No just run the code and it is working as it should
Second: On your sheet you used sometime capital "X" sometimes small "x". I corrected code to recognise both situation.

https://www.dropbox.com/s/w3bicqbgk27dd66/Huck%20JHA.xlsm

Good luck
Andrzej

 

[Sawright]

that is so cool thanks for everything!!!!

Andy

 

[Sawright]

Now I'm trying to create a new "checklist sheet" every time the code is Run. I highlighted my attempt below, but that didn't work

Help



Sub test()


Dim ws As Worksheet
Dim irow, end_row, ShCheck As Long
Dim iName As String




irow = 1
Sheets("checklist").Cells().Clear
Sheets("choose").Activate




Do Until Cells(irow, 1) = Empty




If Not Cells(irow, 1) = Empty Then
If Cells(irow, 2) = "x" Or Cells(irow, 2) = "X" Then
iName = Cells(irow, 1)
ShCheck = 1
For Each ws In Sheets
If LCase(ws.Name) = LCase(iName) Then
ws.Activate
Range(Cells(1, 1), Cells(20, 5)).Copy
Sheets("checklist").Activate
end_row = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Not Cells(end_row, 5) = Empty Then end_row = end_row + 1
Cells(end_row, 1).Select
ActiveSheet.Paste
Cells.EntireColumn.AutoFit
Sheets("choose").Activate
ShCheck = 0
Exit For
End If
Next ws
If ShCheck = 1 Then MsgBox ("DataSheet for " & iName & " not found")
End If
End If




irow = irow + 1
Loop


Sheets("checklist").Select
Application.CutCopyMode = False
Sheets("checklist").Copy After:="choose"
End Sub

 

[skorpionkz]

try this one

https://www.dropbox.com/s/ozfupn1sjgnr0m3/Huck%20JHA.xlsm

 

[Sawright]

It had a runt time error, not sure If I did anything wrong

I do appreciate the help

 

[Sawright]

I can't seem to get it to work, plus once you run it I would like it to be on the sheet it just created instead of back at "Choose sheet"


You have been awesome in this Thanks again

 

[skorpionkz]

I'm sorry for error in my last code.
corrected now:

https://www.dropbox.com/s/ozfupn1sjgnr0m3/Huck%20JHA.xlsm

 

[Sawright]

First, I want to thank you again for your help. I'm learning from this and I appreciate it. I attached a link of what I've done so far. I'm almost complete for what I want it to look like and do. I am stuck again though.

-I would like to use the check boxes I show at the top of the "choose" tab instead of the having to type X in,
-Also I added a Project name and would like the tab generated to bear the project name instead of Checklist and it keeps adding new sheet every time it is done with project name.
-Also I need to keep the header at the top (like it is in first checklist tab)of each generated sheet.
-Not sure you could insert project name at top also of instead of listed in header.


https://www.dropbox.com/s/ixubt0350jaxr7y/Huck%20JHA.xlsm

I'm sure I'm pushing my luck but that should do it.


Thanks

Andy

 

[skorpionkz]

give me couple days for this (xmas time).

Need one clarification:
The results sheet should have name of Project you have on the top right corner?
and if so what code should do if there is already Project with this name? Replace it or add number after Project name?

Marry Christmas,
Andrzej