Help in creating a function or macro to run on data

Sawright

New Member
Joined
Dec 17, 2013
Messages
17
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi Sawright - I just have a min. to let you know that this is definately possible, at least as much as I understand your task. I hope later today I will have a few minutes to put together some draft code for you to look at. Basically you would have code that:
1. Looks at column B
2. Determines if there is an x
3. If Yes - find the tab named in column A, go to that tab, copy cells A1:D20, go to a summary tab, paste
4. Repeat going down column B until there are no more values in column A

I just don't quite have the time to code it right now, but that is how I think you could do it.

Hope this helps.

Happy Excelling,

goesr
 
Upvote 0
Hi Sawright,

try this one

Code:
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" 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, 4)).Copy
                    Sheets("Checklist").Activate
                    end_row = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
                    If Not Cells(end_row, 1) = Empty Then end_row = end_row + 1
                    Cells(end_row, 1).Select
                    ActiveSheet.Paste
                    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


End Sub
 
Last edited:
Upvote 0
That is correct and if you could help with code it would be much appreciated.

Thanks
Andy

Check above code. Additionally it will clear Checklist sheet before adding information there and it will activate message box if sheet for selected product wouldn't be found.

let say if some on will select apple and there is no sheet with name apple then code will show message: DataSheet for apple not found
 
Upvote 0
Thanks so much but now I'm going to show my true ignorance with excel. Where do I insert the code and how do I get it to work? is there a Macro or something. I appreciate all the help, I'm trying to learn this stuff

Thanks
 
Upvote 0
I went under visual basic and inserted code under the first sheet which contains the information to put x in box....is this correct. Can I send you the file would that help?

I appreciate this

Andy
 
Upvote 0
Ok update: Figured out how to run code in visual basic, fixed some errors i could figure out but getting a 1004 error: application defined or object-defined error.
 
Upvote 0
upload the file to some storage service (like dropbox) and give the link. If you don't want everyone to see the file, then PM link to me.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top