Previous Topic Next Topic

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

Closed
cory_boss Posts 8 Registration date Sunday April 2, 2017 Status Member Last seen April 20, 2017 - Updated on Apr 2, 2017 at 05:47 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 24, 2017 at 11:13 AM
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
Related:

4 responses

Answer 1 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 3, 2017 at 11:48 AM
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
0
cory_boss Posts 8 Registration date Sunday April 2, 2017 Status Member Last seen April 20, 2017
Apr 3, 2017 at 06:10 PM
Thank you for this. How do I input this into my cells or worksheet??
0
cory_boss Posts 8 Registration date Sunday April 2, 2017 Status Member Last seen April 20, 2017
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.
0
Answer 2 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 4, 2017 at 11:38 AM
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
0
cory_boss Posts 8 Registration date Sunday April 2, 2017 Status Member Last seen April 20, 2017
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.
0
cory_boss Posts 8 Registration date Sunday April 2, 2017 Status Member Last seen April 20, 2017
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 13, 2017 at 11:17 AM
Hi Cory,

By check mark, do you mean a Form Control's Check box?
0
cory_boss Posts 8 Registration date Sunday April 2, 2017 Status Member Last seen April 20, 2017 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
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.
0
Answer 3 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 18, 2017 at 11:58 AM
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
0
cory_boss Posts 8 Registration date Sunday April 2, 2017 Status Member Last seen April 20, 2017
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
Answer 4 / 4
cory_boss Posts 8 Registration date Sunday April 2, 2017 Status Member Last seen April 20, 2017
Apr 20, 2017 at 11:30 AM
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.

0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0