Report

Drop down menu, copy/transfer 2 new sheets only if cell has data

Ask a question cory_boss 8Posts Sunday April 2, 2017Registration date April 20, 2017 Last seen - Last answered on Apr 24, 2017 at 11:13 AM by TrowaD
Hello,

I am trying to create a sheet that references items if they are selected from another sheet from a drop down menu. IF they are selected then it transfers the data and if they are not selected and it is blank then the cell only fills up if data in the column has been selected from drop down menu. if More then one data is in the column then I want it to fill up all the columns in the reference sheets with data that is selected.

For Example: say I have $200 and a $100 and $150 in the column and a whole bunch of blanks in between them then in cell A1 I want it to have the $200 and then in cell A2 to find the next non blank cell and reference it to A2 which would be $100 and then in A3 find the next non blank cell and reference it which would be $150.

I know this sounds confusing but hopefully it makes sense to one of you to help my find a formula to use or the necessary steps to create this
See more 
Helpful
+0
plus moins
Hi Cory,

When the amounts are in column B, then the code below will place them in column A, without blanks:
Sub RunMe()
For Each cell In Range("B:B")
    If cell.Value <> vbNullString Then
        cell.Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next cell
End Sub


Best regards,
Trowa
cory_boss 8Posts Sunday April 2, 2017Registration date April 20, 2017 Last seen - Apr 3, 2017 at 06:10 PM
Thank you for this. How do I input this into my cells or worksheet??
Reply
cory_boss 8Posts Sunday April 2, 2017Registration date April 20, 2017 Last seen - Apr 3, 2017 at 06:22 PM
For example on my sheet I need the column from range H18-H78 if they have data filled out and skip the blank cells to be transferred to another sheet in columns H15-H33 with no repeating. but only the cells with data will be transferred. I have never worked with the developer side of excel to input formulas.
Reply
Leave a comment
Helpful
+0
plus moins
Hi Cory,

This is not called a formula but a macro code. For it to work make sure that the sheet references in the code match the ones in your file. The source sheet (the sheet with your range H18-H78) is called 'Sheet1' in the code, found on code line 2. The destination sheet is called 'Sheet2' in the code, found on code line 6, 7, 8 and 13.


Sub RunMe()
Sheets("Sheet1").Select
For Each cell In Range("H18:H78")
    If cell.Value <> vbNullString Then
        cell.Copy
        If Sheets("Sheet2").Range("H15").Value = vbNullString Then
            Sheets("Sheet2").Range("H15").PasteSpecial
        Else: Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
        End If
    End If
Next cell
Sheets("Sheet2").Range("H15:H33").RemoveDuplicates Columns:=1, Header:=xlNo
Application.CutCopyMode = False
End Sub


How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.

Let me know if further assistance is desired.

Best regards,
Trowa
cory_boss 8Posts Sunday April 2, 2017Registration date April 20, 2017 Last seen - Apr 4, 2017 at 05:52 PM
OK yes we are getting so close. I appreciate your time and assistance. Ok I got that to work but now I would like to make a change. and don't know where and what to input.

I have H18:H78 as a drop down menu with a blank and a check mark. If the check mark is selected then I would like the item name of the check marked box row to be transferred to sheet 2 which (and once again only the items on sheet 1 that are checked to be transferred). the named items are in column B19:B74. so I believe that is a IF statement but for some reason when I try and edit, it doesn't work for me so I must be inputting something wrong.

thank you so much. once again it I'm so close.
Reply
cory_boss 8Posts Sunday April 2, 2017Registration date April 20, 2017 Last seen - Apr 10, 2017 at 08:24 PM
OK yes we are getting so close. I appreciate your time and assistance. Ok I got that to work but now I would like to make a change. and don't know where and what to input.

I have H18:H78 as a drop down menu with a blank and a check mark. If the check mark is selected then I would like the item name of the check marked box row to be transferred to sheet 2 which (and once again only the items on sheet 1 that are checked to be transferred). the named items are in column B19:B74. so I believe that is a IF statement but for some reason when I try and edit, it doesn't work for me so I must be inputting something wrong.

thank you so much. once again it I'm so close.
Reply
TrowaD 2259Posts Sunday September 12, 2010Registration date ModeratorStatus August 15, 2017 Last seen - Apr 13, 2017 at 11:17 AM
Hi Cory,

By check mark, do you mean a Form Control's Check box?
Reply
cory_boss 8Posts Sunday April 2, 2017Registration date April 20, 2017 Last seen - Apr 13, 2017 at 12:12 PM
I will send you a message with a sample photo of a section of my sheet. so you can see.
Reply
Leave a comment
Helpful
+0
plus moins
Hi Cory,

That looks like a Wingding symbol, which is recognised by Excel as 'ü'.

But I'm kinda lost as to what you want to achieve, so the code might not be what you are after.

How I see it now is that you want to check sheet1 range H18:H78 for check marks. When found, copy column B (Item name, as requested in post 6) and column I (amount as requested in sample photo) and paste it to the first available cell in sheet2 column H and I starting from row 15.

Why the check mark range H18:H78 is different then the item name range B19:B74 is beyond my understanding.

Here is the code:
Sub RunMe()
Dim mR As Range
Sheets("Sheet1").Select
For Each cell In Range("H18:H78")
    If cell.Value = "ü" Then
        Set mR = Union(Range("B" & cell.Row), Range("I" & cell.Row))
        mR.Copy
        If Sheets("Sheet2").Range("H15").Value = vbNullString Then
            Sheets("Sheet2").Range("H15").PasteSpecial
        Else: Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
        End If
    End If
Next cell
Application.CutCopyMode = False
End Sub


Best regards,
Trowa
cory_boss 8Posts Sunday April 2, 2017Registration date April 20, 2017 Last seen - Apr 18, 2017 at 05:17 PM
sorry about sending the last one as a message, just was the only way I found to send a photo but later realized what to do.

Ok so for what you have sent to me when I total up on Sheet 2 the cells range H15:H36 it adds up the numbers from the check mark boxes on sheet 1 but the dollar amounts aren't visible for some reason on sheet 2 but yet the total appears as though it is getting the right numbers.

In the photo I sent the check marks are in the column of H18 to H74 and I only want once checked off the dollar amount from column i to transfer to the sheet 2 H15:H36. it seems as though it is working but again it isn't showing the amount transferred from sheet 1 to sheet 2. I don't want to worry about any other columns other then what i have sent and then if i need it for other columns i will create a new module and edit it for the columns needed.

But it isn't 100% working and don't know where the error lies.

Thank you for your help and response again and look forward to your solution.
Reply
TrowaD 2259Posts Sunday September 12, 2010Registration date ModeratorStatus August 15, 2017 Last seen - Apr 20, 2017 at 10:47 AM
Hi Cory,

'the dollar amounts aren't visible for some reason on sheet 2 but yet the total appears as though it is getting the right numbers. '

That seems like a formatting issue. Select the cells, Ctrl+1, select the dollar format.

Does that solve your query, or did I misunderstand?

Best regards,
Trowa
Reply
Leave a comment
Helpful
+0
plus moins
Hello Trowa,

no they are formatted for currency and the dollar format is selected.

I have attached a photo for what is happening. I don't know what else I could be doing wrong.

TrowaD 2259Posts Sunday September 12, 2010Registration date ModeratorStatus August 15, 2017 Last seen - Apr 24, 2017 at 11:13 AM
Hi Cory,

That seems like the code is not doing anything and you are using a formula refering to sheet1 to get the total at the bottom.

Those check marks are wingding symbols, right?

Maybe it is time for you to upload your file, so I can see what is happening instead of guessing. When you choose to do so, you can use a free file sharing site like www.speedyshare.com or ge.tt (careful with sensitive data) and post back the download link.

Best regards,
Trowa
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!