-1

This is my first time posting, however I have used this website a lot in my very short and novice career writing some very basic Macro's into the excel files I use at work. It has been incredibly handy, so I am very thankful!

My current problem.... I have been using "push button" macros to fill data from one page to another. This is pretty easy for me and i feel is a "safe" method for those of us who are self-teaching (via google).

This time, I want to essentially do the same thing but with a drop down list, instead of the push button. However, I am unsure how to write the macro to be conditional based on what is selected from the drop down.

The drop down list has three selections. Each selection will copy data from a different Sheet inside the workbook.

For example (i work in the fashion industry)

I have three size ranges to select from the drop down list. 6-18 XXS-XXL XS/S-L/XL

Based on selecting one of these size ranges they all correspond to a Sheet in the workbook where i will pull information from. In order as above they correspond to Sheet "G1", "G2", and G3. Based on the drop down selection, for example 6-18, will copy data from sheet "G1" to another Sheet named "Parameter". I also need to be able to define the range of cells as i am only copying some of the data from sheet G1, to the parameter page.

Please see what I have so far...

Sub DropDown1_Change()

    'DropDown Select. 6-18
    'Activate the destination worksheet
    Sheets("PARAMETER").Activate
    'Select the target range
    Range("C10:P67").Select
    'sbClearCells()
    Range("C10:P67").Clear
    'Copy the data
    Sheets("G1").Range("C10:P67").Copy
    'Activate the destination worksheet
    Sheets("PARAMETER").Activate
    'Select the target range
    Range("C10:P67").Select
    'Paste in the target destination
    ActiveSheet.Paste
    Application.CutCopyMode = False  

    'DropDown Select. XS/S-L/XL
    'Activate the destination worksheet
    Sheets("PARAMETER").Activate
    'Select the target range
    Range("C10:P67").Select
    'sbClearCells()
    Range("C10:P67").Clear
    'Copy the data
    Sheets("G2").Range("C10:P67").Copy
    'Activate the destination worksheet
    Sheets("PARAMETER").Activate
    'Select the target range
    Range("C10:P67").Select
    'Paste in the target destination
    ActiveSheet.Paste
    Application.CutCopyMode = False  

    'DropDown Select. XXS-XXL
    'Activate the destination worksheet
    Sheets("PARAMETER").Activate
    'Select the target range
    Range("C10:P67").Select
    'sbClearCells()
    Range("C10:P67").Clear
    'Copy the data
    Sheets("G3").Range("C10:P67").Copy
    'Activate the destination worksheet
    Sheets("PARAMETER").Activate
    'Select the target range
    Range("C10:P67").Select
    'Paste in the target destination
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

Currently it is running the code, but it is only running the last paragraph of script. I need a bit of help to get it to recognize the different selection.

Any help would be greatly appreciated.

Thanks so much, Katherine

Community
  • 1
  • 1
MissKatherineEmma
  • 43
  • 1
  • 6
  • This is not a code writing service. Please show what you have so far and why it's not working. – ashleedawg Apr 12 '18 at 02:07
  • Hi @ashleedawg. I attempted to add it into the initial post, but i couldn't get it to work. I will give it another go. – MissKatherineEmma Apr 12 '18 at 02:34
  • Since you are writing to the same cells in `Sheets("PARAMETER"). Range("C10:P67") ` you will overwrite the data every time you select a different size in the DropDown box. Also what cell is your combobox in? – GMalc Apr 12 '18 at 03:53
  • Hi @GMalc. The cell my combobox is in is C4, on a worksheet called "main". It isn't a problem for me that the data is overwritten as i am really just copying a bunch of formulas from my G1, G2, G3 pages, and these all link to the same reference, which is on the "data" page which filter to "parameter" page, and all of the info on the "data" page links to various charts i have set up VIA three different "vlookup" tables (based on the size range), and also based on another "Option button" selection i have on the Main page.It sounds unnecessary but this is the best workaround for what i need. – MissKatherineEmma Apr 12 '18 at 05:25

2 Answers2

0

What form of dropdown list are you using? The problem you are having is that it runs through the entire code and you only see the final thing that was pasted on the range.

For dropdown lists in excel I will typically default to a cell with data validation on it with the list control. This will tell you which portion of the code should be run. This can be achieved with if statements for example if the validation cell was in "A1" your code would be modified to this.

Sub CopyRanges()

    Dim selectedValue 
    'Change the range to where you end up putting the dropdown
    selectedValue = Sheets("Parameter").Range("A1").value

    If selectedValue = "6-18" Then
        'Activate the destination worksheet
        Sheets("PARAMETER").Activate
        'Select the target range
        Range("C10:P67").Select
        'sbClearCells()
        Range("C10:P67").Clear
        'Copy the data
        Sheets("G1").Range("C10:P67").Copy
        'Activate the destination worksheet
        Sheets("PARAMETER").Activate
        'Select the target range
        Range("C10:P67").Select
        'Paste in the target destination
        ActiveSheet.Paste
        Application.CutCopyMode = False  
    End If

    If selectedValue = "XS/S-L/XL" Then
        'Activate the destination worksheet
        Sheets("PARAMETER").Activate
        'Select the target range
        Range("C10:P67").Select
        'sbClearCells()
        Range("C10:P67").Clear
        'Copy the data
        Sheets("G2").Range("C10:P67").Copy
        'Activate the destination worksheet
        Sheets("PARAMETER").Activate
        'Select the target range
        Range("C10:P67").Select
        'Paste in the target destination
        ActiveSheet.Paste
        Application.CutCopyMode = False  
    End If     

    If selectedValue = "XS/S-L/XL" Then
        'Activate the destination worksheet
        Sheets("PARAMETER").Activate
        'Select the target range
        Range("C10:P67").Select
        'sbClearCells()
        Range("C10:P67").Clear
        'Copy the data
        Sheets("G3").Range("C10:P67").Copy
        'Activate the destination worksheet
        Sheets("PARAMETER").Activate
        'Select the target range
        Range("C10:P67").Select
        'Paste in the target destination
        ActiveSheet.Paste
        Application.CutCopyMode = False 
    End If
End Sub

As this stands you will still need to press a button to call the function. If you want it to run every time the cell is changed I would look at this post.

Note: A similar affect could be achieved with a combo box.

gcro006
  • 41
  • 4
  • Thank you so much for the fast reply! I am using a Combo Box. Your changes work well - thank you so much. I try so hard to figure it out but it is difficult when what i am creating is so specific its hard to follow the examples other people use and apply it to your own circumstances! I have a separate worksheet i am using as a "data" sheet which i created a reference to the drop down list, and then just referenced this cell.. I had a feeling a cell reference was the way. At this stage i am wanting to still "press a button" but I will have a read of the suggested post as well. Thank you. – MissKatherineEmma Apr 12 '18 at 03:41
0

Edited. This code uses Select Case, since I don't know what cell your ComboBox is in, I am using B2. Like i said in my comment every time you select a size your data will be over written in Sheets("PARAMETER"). Range("C10:P67"). I also got ride of your extra code pieces. 

Private Sub ComboBox1_Click()
     Select Case ComboBox1.Value

        Select Case Range("C4")
            Case "6-18": Size_6_18 'selecting will call the macro Size_6_18 etc. etc. for the other 2 cases
            Case "XS/S-L/XL": Size_XS_XL
            Case "XXS-XXL": Size_XXS_XXL
        End Select
    End If
End Sub
Sub Size_6_18()
    Sheets("PARAMETER").Range("C10:P67").Clear
    Sheets("PARAMETER").Range("C10:P67").Value(11) = Sheets("G1").Range("C10:P67").Value(11) '(11) copies formats etc. 
End Sub
Sub Size_XS_XL()
    Sheets("PARAMETER").Range("C10:P67").Clear
    Sheets("PARAMETER").Range("C10:P67").Value(11) = Sheets("G2").Range("C10:P67").Value(11)
End Sub
Sub Size_XXS_XXL()
    Sheets("PARAMETER").Range("C10:P67").Clear
    Sheets("PARAMETER").Range("C10:P67").Value(11) = Sheets("G3").Range("C10:P67").Value(11)
End Sub
GMalc
  • 2,608
  • 1
  • 9
  • 16
  • Hi GMalc, Sorry I didn't see this when i commented before. This works much better as when you change the selection is isn't "running the code and dragging me to the page the code is run on" I am able to select on the "Main" worksheet and remain on this worksheet. The only problem with the above is is hasn't copied over my formatting of text, font size, colour. etc.. Any workaround for this ? – MissKatherineEmma Apr 12 '18 at 05:35
  • Yes, use the optional argument for `.Value`; change all `.Value` in the sub to `.Values(11)`, this should copy the cell formats for the range from "G2". If my answer has help you, please up-vote. – GMalc Apr 12 '18 at 14:20
  • As a Note: .Value(11) will copy the values, formatting, formulas and names of the specified Range, it will not copy the row height or column with. Primarily for xml, it work fine with basic code, i have not run into any issues when copying formats, etc. To copy the row height you would add an additional line of code; `Sheets("PARAMETER").Range("C10:P67").RowHeight = Sheets("G3").Range("C10:P67").RowHeight` and another line of code for column with. – GMalc Apr 12 '18 at 14:51
  • Hi GMalc! Thanks so much that worked well.. I did a test just now and it the formatting issue was fixed but It wasnt copying across the formulas.. I did a bit of reading and added the line; Sheets("PARAMETER").Range("C10:P67").Formula = Sheets("G3").Range("C10:P67").Formula which worked a treat! Thank you for your help! :) – MissKatherineEmma Apr 13 '18 at 00:04